{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aa460049-640b-4961-981a-affc463c8a3b",
   "metadata": {},
   "source": [
    "# 1 准备工作"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3956f857-03ce-481c-bb42-abbe139311bd",
   "metadata": {},
   "source": [
    "## 1.1 安装相关包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3fb17e0f-b216-45a1-874a-89b4aedbd88e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !apt update\n",
    "# !apt install unrar -y\n",
    "# !apt install mdbtools -y\n",
    "# !apt install default-jre -y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "acee0a2a-18b5-463a-adb4-d636acf3ea62",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !java --version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bb4535f7-9daf-474e-86a0-301054e782c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install pytz\n",
    "# !pip install rarfile\n",
    "# !pip install jaydebeapi\n",
    "# !pip install pyodbc\n",
    "# !pip install uuid\n",
    "# !pip install tenacity"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78abd657-675f-4d8c-8251-52752fef53b5",
   "metadata": {},
   "source": [
    "## 1.2 加载包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3a0df1bd-91b1-476d-bf6a-9c17983db382",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import pytz\n",
    "from datetime import datetime\n",
    "import json, re, gc\n",
    "import glob\n",
    "import os, sys, rarfile\n",
    "from tqdm import tqdm\n",
    "# from typing import Dict, Any\n",
    "from pathlib import Path\n",
    "\n",
    "import platform\n",
    "import jaydebeapi\n",
    "import jpype\n",
    "import jpype.imports\n",
    "from jpype.types import *\n",
    "import subprocess\n",
    "import pyodbc, io\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import math\n",
    "from concurrent.futures import ThreadPoolExecutor, as_completed\n",
    "import torch\n",
    "import pickle\n",
    "# from openai import OpenAI\n",
    "import warnings, logging\n",
    "sys.path.append('./')\n",
    "\n",
    "from utils.load_access_data import *\n",
    "from utils.file_unrar import *\n",
    "# from utils.data_mapping import *\n",
    "# from utils.data_mapping_36country3 import *\n",
    "# from utils.invoke_llm import *\n",
    "from utils.file_process import *\n",
    "from utils.bash_mapping import *\n",
    "from utils.logging_config import setup_logger\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "logger = setup_logger('Data Goverance')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0d22cb7f-a935-4e82-bb54-a1cc22e6d6a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1e70e112-297d-41ad-b94f-ef1c9bb884e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:21:52,953 - Data Goverance - INFO - 2025-08-20 21:21:52.952960+08:00\n"
     ]
    }
   ],
   "source": [
    "# 计时\n",
    "tz_beijing = pytz.timezone('Asia/Shanghai')\n",
    "bj_now = datetime.now(tz_beijing)\n",
    "dt_now = datetime.strptime(datetime.strftime(bj_now, '%Y-%m-%d'), '%Y-%m-%d')\n",
    "logger.info(bj_now)\n",
    "\n",
    "all_start=time.time()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14176eb0-f67a-4053-96f8-ff299df631ae",
   "metadata": {},
   "source": [
    "## 1.3 准备全局变量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2baa7fe2-b85d-4299-b235-d672e338ab78",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Config:\n",
    "    target_cols = ['channelno', 'dataid', 'iesign', 'datatype', 'writeoffflag'\n",
    "            , 'writeoffdataid', 'outputdate', 'origincountrycode', 'origincountry', 'countrycodeofdelivery'\n",
    "            , 'countryofdelivery', 'importername', 'importeraddress', 'importercontact', 'suppliername'\n",
    "            , 'supplieraddress', 'suppliercontact', 'hscode', 'hscodedescription', 'commoditydescription'\n",
    "            , 'totalcifvalue', 'totalfobvalue', 'grossweight', 'netweight', 'quantity'\n",
    "            , 'quantityunit', 'teu', 'importer_forwarderagent', 'supplier_forwarderagent', 'abnormaldata'\n",
    "            , 'portofloading', 'portofdestination', 'loadingcountrycode', 'loadingcountry', 'transportterm'\n",
    "            , 'tradeterm', 'paymentterm', 'carrier', 'containerno', 'vesselname'\n",
    "            , 'brand', 'version', 'country', 'IMPORTER_ID', 'SUPPLIER_ID', 'cif_currency', 'fob_currency']\n",
    "config = Config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "90881866-9cbb-4442-a5f6-7217e901ce3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 加载国家映射表\n",
    "country_code = pd.read_csv('国家代码表.csv')\n",
    "country_code_dict = country_code.loc[:,['英文国名','中文国名']].to_dict('records')\n",
    "country_code_dict_detail = country_code.to_dict('records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "67fbe1a5-8a7b-4d95-ae58-ef5a97913cc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 加载36个国家码\n",
    "data_country36_code = pd.read_csv('数据36国家代码表.csv')\n",
    "data_country36_dict = data_country36_code.to_dict('records')\n",
    "\n",
    "country36_dict = dict()\n",
    "for x in data_country36_dict:\n",
    "    country36_dict[x.get('中文国名')]={'英文国名': x.get('英文国名'),'国别编码':x.get('国别编码')}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ad5b28d4-f134-4460-810a-468a9ebbd98c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 加载各国映射函数\n",
    "# function_map = get_function_map(data_country36_dict)\n",
    "# # function_map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ff0099c1-7091-40f2-8fe0-00618ab32c97",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理的中间结果，\n",
    "model_result = pickle.load(open('result/model_rslt_05_20250810.pickle', 'rb'))\n",
    "# model_result = dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf6830bc-d070-4f45-a9de-13fceee9f39b",
   "metadata": {},
   "source": [
    "# 2 原始文件批量解压\n",
    "\n",
    "原始数据保存路径： dataset/data_20250724/2025年1-3月汇总\n",
    "\n",
    "批量解压至： dataset/data_20250724/20251t3_unrar”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4fe99ca6-b41b-40d2-981c-880874d3acee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:21:52,979 - Data Goverance - INFO - number of rar_files:153, number of non_rar_files:1\n"
     ]
    }
   ],
   "source": [
    "# 定义要遍历的根目录\n",
    "root_dir = Path('dataset/data_20250724/2025年1-3月汇总')\n",
    "dir_paths, file_paths = get_dir_files(root_dir)\n",
    "\n",
    "# 获取需要解压的文件目录\n",
    "rar_file_paths = [x for x in file_paths if str(x).split('.')[-1]=='rar']\n",
    "nrar_file_paths = [x for x in file_paths if str(x).split('.')[-1]!='rar']\n",
    "logger.info(f'number of rar_files:{len(rar_file_paths)}, number of non_rar_files:{len(nrar_file_paths)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "90e89919-162e-4eda-b93f-e968e47a71d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 批量解压至“dataset/data_20250724/20251t3_unrar”\n",
    "# # 请谨慎执行，如果解压过请不要再运行\n",
    "# extract_rar_batch(file_paths, source_str='2025年1-3月汇总', replace_str='20251t3_unrar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e868c9d1-a325-4ffc-a0c7-baabdc443541",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9ca558fd-2b08-4340-847b-5c0cf25c3af4",
   "metadata": {},
   "source": [
    "# 3 解压的各种格式数据统一转为csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20fdd3cb-ca1a-4c3e-af5e-de00d0aaa8f6",
   "metadata": {},
   "source": [
    "\n",
    "添加数据主键 dataid\n",
    "添加数据主键 country\n",
    "添加数据主键 iesign\n",
    "\n",
    "转换后数据路径为：dataset/data_20250724/20251t3_src"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "61893540-28d0-4259-99a1-a4b5f4f2b1f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['阿根廷', '埃塞俄比亚', '巴基斯坦', '巴拉圭', '巴拿马', '博兹瓦纳', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '印度', '英国', '越南', '智利']\n",
    "country_list = ['智利', '坦桑尼亚', '乌克兰']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c80de384-6c66-4822-bbc9-fd04180d08c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取文件目录，区分出口、进口数据\n",
    "from_dir = Path('dataset/data_20250724/20251t3_unrar')\n",
    "to_dir = Path('dataset/data_20250724/20251t3_src')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e0a56942-1490-4c35-b474-412c384a5999",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# file_tree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6823a06f-77fe-41c0-a7fd-a908e70d4a3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['阿根廷']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "805beda6-f192-4982-8690-72e74c96cde3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 慎重跑这个结果，会全量抽取原始数据\n",
    "# all_country_cols_dict = get_source_csv(country_list, from_dir, to_dir, country36_dict)\n",
    "\n",
    "# model_result['all_country_cols_dict']=all_country_cols_dict\n",
    "# pickle.dump(model_result, open('result/model_rslt_00_all_country_cols_dict.pickle', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "94b8e2f4-8a1d-4233-aad8-4613bbb8c80f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 36个国家数据的列名\n",
    "# model_result = pickle.load(open('result/model_rslt_00_all_country_cols_dict_20250804.pickle', 'rb'))\n",
    "model_result = pickle.load(open('result/model_rslt_05_20250810.pickle', 'rb'))\n",
    "\n",
    "country36_cols_dict = model_result['all_country_cols_dict']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a43eac43-2933-4ab8-abc4-c93fb024f3fe",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# country36_cols_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96353e48-3495-4199-ac9d-59939b21ed43",
   "metadata": {},
   "source": [
    "# 4 更正数据月份至version字段"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2e90119f-c8fd-4702-a6ea-d72bfae0f723",
   "metadata": {},
   "outputs": [],
   "source": [
    "root_dir = Path('dataset/data_20250724/20251t3_src')\n",
    "dir_paths, file_paths = get_dir_files(root_dir)\n",
    "file_tree = distinct_ie_data(root_dir, country_list)\n",
    "\n",
    "save_dir = Path('dataset/data_20250724/20251t3_src_version')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d40bc16e-6512-4991-b612-5ea0e8734c08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !rm -rf dataset/data_20250724/20251t3_src_version/秘鲁-空运\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/秘鲁-海运\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/科特迪瓦\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/莱索托\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/乌干达\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/乌克兰\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/加纳\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/喀麦隆\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/菲律宾\n",
    "# !rm -rf dataset/data_20250724/20251t3_src_version/马拉维"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5882d3db-5198-4d42-8815-ec544887a908",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['阿根廷', '埃塞俄比亚', '巴基斯坦', '巴拉圭', '巴拿马', '博兹瓦纳', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '印度', '英国', '越南', '智利']\n",
    "# country_list = ['秘鲁-空运', '秘鲁-海运', '科特迪瓦', '莱索托', '乌干达', '乌克兰'\n",
    "#     , '加纳', '喀麦隆', '菲律宾', '马拉维']\n",
    "# country_list = ['尼日利亚', '博兹瓦纳']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0a30c055-d35e-4ab2-85d6-c722bfd51e49",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# get_data_month2(root_dir, save_dir, country_list, country36_cols_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "66b196b1-698e-4c71-aad2-38b5c1912ad6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# !head -3 dataset/data_20250724/20251t3_src_version/印度/import_paths/INDIA_IMPORT_202502_2105B.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d25bd35-e78d-4277-a748-19468983a494",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "878b0cc6-58ac-4ba3-a3b0-85949f5e4b6d",
   "metadata": {},
   "source": [
    "# 5 映射函数实现"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "865d2fc0-a4d5-4955-b3dc-5ba492496848",
   "metadata": {},
   "source": [
    "数据来源路径： 'dataset/data_20250724/20251t3_src_version'\n",
    "\n",
    "csv结果文件保存路径： 'dataset/data_20250724/20251t3_csv_save'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3239b6e6-e7cc-498e-af56-39befcfb5908",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['阿根廷', '埃塞俄比亚', '巴基斯坦', '巴拉圭', '巴拿马', '博兹瓦纳', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '印度', '英国', '越南', '智利']\n",
    "# country_list = ['秘鲁-空运', '秘鲁-海运', '科特迪瓦', '莱索托', '乌干达', '乌克兰'\n",
    "#     , '加纳', '喀麦隆', '菲律宾', '马拉维']\n",
    "# country_list = ['阿根廷', '埃塞俄比亚', '巴基斯坦', '巴拉圭', '巴拿马', '博兹瓦纳', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '英国', '智利']\n",
    "\n",
    "# country_list = ['尼日利亚', '博兹瓦纳']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "21ed1ef0-ccdf-45ec-ab8d-888e95991b7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 姚威\n",
    "# country_list = ['印度', '巴拉圭', '菲律宾', '哥伦比亚', '莱索托', '乌拉圭','美国', '阿根廷'\n",
    "#                 '纳米比亚', '尼日利亚', '坦桑尼亚', '乌兹别克斯坦']\n",
    "\n",
    "# # 张煜卓\n",
    "# country_list = ['越南', '厄瓜多尔', '科特迪瓦', '肯尼亚', '斯里兰卡', '乌克兰', '孟加拉', '英国'\n",
    "#                 , '智利', '哥斯达黎加',  '博兹瓦纳' , '乌干达']\n",
    "\n",
    "# # 陈武杰\n",
    "# country_list = ['俄罗斯陆运', '埃塞俄比亚', '加纳', '喀麦隆', '马拉维', '墨西哥', '秘鲁', '秘鲁-海运'\n",
    "#                 , '秘鲁-空运', '巴拿马', '哈萨克斯坦', '巴基斯坦']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "dec4e626-1397-44f3-9ca8-ccae14e99d68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['尼日利亚', '斯里兰卡', '乌拉圭']\n",
    "# country_list = ['英国', '孟加拉']\n",
    "# country_list = ['埃塞俄比亚', '加纳', '马拉维', '墨西哥', '巴基斯坦', '巴拿马 进口', '秘鲁-海运 出口', '英国']\n",
    "\n",
    "# country_list = ['厄瓜多尔', '科特迪瓦', '斯里兰卡', '乌克兰', '越南']\n",
    "# country_list = ['阿根廷', '菲律宾']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "bc5f644a-77ba-4e3c-93d8-a12b96b74bdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 获取文件目录，区分出口、进口数据\n",
    "# root_dir = Path('dataset/data_20250724/20251t3_src_version')\n",
    "# dir_paths, file_paths = get_dir_files(root_dir)\n",
    "# file_tree = distinct_ie_data(root_dir, country_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d39be6a6-b5be-4f91-b42c-dc022f9088a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # csv结果文件保存路径\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_save')\n",
    "# csv_files_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bb9f8378-d91f-4f3c-b250-8028b0a623bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e099f39f-f139-4b3f-b29c-a0aca14fd150",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "aca38f2c-0867-4e42-bddf-28eae38a0a66",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result1 = pickle.load(open('result/model_rslt_00_all_country_cols_dict_20250804.pickle', 'rb'))\n",
    "# all_country_cols_dict = model_result1['all_country_cols_dict']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "20d919a5-1d71-4160-8fda-f81d8ae6bc40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result1.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "910df535-b69f-4136-b33b-26137928e9b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# all_country_cols_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7630ed01-fe42-4e72-a959-60b3058f99a4",
   "metadata": {},
   "source": [
    "## 5.1 映射数据校验"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c517d8f-3418-4ece-b0bb-5afebe00e6e7",
   "metadata": {},
   "source": [
    "### 5.1.1 出口数据校验"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "98f7c022-2d8c-4f73-a1d1-62898e8f7257",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 出口数据校验\n",
    "# start = time.time()\n",
    "# for ctry in tqdm(country_list):\n",
    "\n",
    "#     logger.info(ctry)\n",
    "#     if file_tree[ctry]['export_paths']:\n",
    "#         exp_path = file_tree[ctry]['export_paths'][0]\n",
    "#         logger.info(exp_path)\n",
    "#         exp_file = str(exp_path).split('/')[-1]\n",
    "#         func_map = [x for x in function_map if x.get('中文国名')==ctry]\n",
    "#         target_tab = func_map[0].get('export_fuc')(exp_path, all_country_cols_dict[ctry]['export_paths'])\n",
    "#         # print(target_tab.head())\n",
    "# end = time.time()\n",
    "# logger.info(f'all data takes {round(end - start)/60} minutes')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1ffd961-2ac2-4fa3-9faa-455de66e0803",
   "metadata": {},
   "source": [
    "### 5.1.2 进口数据校验"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "7019d3b7-20d9-47b5-ba65-1ed4a511ad75",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 进口数据校验\n",
    "# start = time.time()\n",
    "# for ctry in tqdm(country_list):\n",
    "\n",
    "#     logger.info(ctry)\n",
    "#     if file_tree[ctry]['import_paths']:\n",
    "#         imp_path = file_tree[ctry]['import_paths'][0]\n",
    "#         logger.info(imp_path)\n",
    "#         exp_file = str(imp_path).split('/')[-1]\n",
    "#         func_map = [x for x in function_map if x.get('中文国名')==ctry]\n",
    "#         target_tab = func_map[0].get('import_fuc')(imp_path, all_country_cols_dict[ctry]['import_paths'])\n",
    "#         # print(target_tab.head())\n",
    "# end = time.time()\n",
    "# logger.info(f'all data takes {round(end - start)/60} minutes')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4deabdf1-77b2-429a-89c5-7519a58b81d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# all_country_cols_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4530484a-9a00-471f-a938-f4533cbdd9c6",
   "metadata": {},
   "source": [
    "## 5.2 出口、进口批量跑映射函数"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f11e091-6559-4b9f-a0f0-6c97d75c3f20",
   "metadata": {},
   "source": [
    "### 5.2.1 出口跑批"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "416fd614-ccfa-40ac-af3b-d0c8f950dc59",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 出口跑批\n",
    "# start = time.time()\n",
    "# for ctry in tqdm(country_list):\n",
    "#     logger.info(ctry)\n",
    "#     if file_tree[ctry]['export_paths']:\n",
    "#         for exp_path in tqdm(file_tree[ctry]['export_paths']):\n",
    "#             logger.info(exp_path)\n",
    "#             exp_file = str(exp_path).split(os.sep)[-1]\n",
    "#             func_map = [x for x in function_map if x.get('中文国名')==ctry]\n",
    "#             target_tab = func_map[0].get('export_fuc')(exp_path, all_country_cols_dict[ctry]['export_paths'])\n",
    "#             # 目标表保存至csv\n",
    "#             logger.info(f'target_tab shape: {target_tab.shape}')\n",
    "#             save_data_to_csv(target_tab, csv_files_path, file_name=exp_file, country_code=ctry, IE_tag='EXPORT')\n",
    "#             # print(target_tab.head())\n",
    "#             del target_tab\n",
    "#             gc.collect()\n",
    "# end = time.time()\n",
    "# logger.info(f'all data takes {round((end - start)/60, 2)} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "7d994664-0c77-45d3-a8a2-0d1f1b8a6b11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# bash_mapping_export2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "bfcbf49a-bf8e-4945-b23b-d0d5647b94c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取文件目录，区分出口、进口数据\n",
    "root_dir = Path('dataset/data_20250724/20251t3_src_version')\n",
    "\n",
    "# csv结果文件保存路径\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_save')\n",
    "csv_files_path = Path('dataset/data_20250724/20251t3_csv_save2')\n",
    "\n",
    "# model_result1 = pickle.load(open('result/model_rslt_00_all_country_cols_dict_20250804.pickle', 'rb'))\n",
    "model_result1 = pickle.load(open('result/model_rslt_05_20250810.pickle', 'rb'))\n",
    "all_country_cols_dict = model_result1['all_country_cols_dict']\n",
    "\n",
    "# country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "# country_list = ['印度', '巴基斯坦', '巴拿马', '博兹瓦纳', '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳'\n",
    "#                 , '喀麦隆', '莱索托', '马拉维', '孟加拉', '秘鲁-海运', '秘鲁-空运', '墨西哥', '坦桑尼亚', '乌干达'\n",
    "#                 , '乌兹别克斯坦']\n",
    "# country_list = ['俄罗斯陆运', '肯尼亚']\n",
    "country_list = ['智利', '坦桑尼亚', '乌克兰']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "6a365e16-9c5b-4938-9729-bf4a71422aa6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:21:53,104 - Bash Mapping - INFO - 乌克兰\n",
      "2025-08-20 13:21:53,104 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/export_paths/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:21:53,105 - Bash Mapping - INFO - 坦桑尼亚\n",
      "2025-08-20 13:21:53,106 - Bash Mapping - INFO - 智利\n",
      "2025-08-20 13:21:53,106 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/export_paths/202501-EXP-RAW.csv\n",
      "2025-08-20 13:21:53,106 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/智利/export_paths/202502-EXP.csv\n",
      "2025-08-20 13:21:53,250 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:21:53,279 - Data Mapping - INFO - Export data mapping and cleaning takes 0.0 minutes\n",
      "2025-08-20 13:21:53,301 - Bash Mapping - INFO - target_tab shape: (9990, 47)\n",
      "2025-08-20 13:21:53,564 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:21:53,640 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/export_paths/202503-EXP-RAW.csv\n",
      "2025-08-20 13:21:53,759 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:21:53,787 - Data Mapping - INFO - Export data mapping and cleaning takes 0.0 minutes\n",
      "2025-08-20 13:21:53,794 - Bash Mapping - INFO - target_tab shape: (7761, 47)\n",
      "2025-08-20 13:21:53,994 - Data Mapping - INFO - imp_df columns: ['DAY' 'MONTH' 'YEAR' 'CONTROL_ID' 'CUSTOMS' 'EXPORTER_ID' 'VERIFIER'\n",
      " 'EXPORTER' 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT' 'VARIETEY' 'BRAND' 'DESC'\n",
      " 'DEST_COUNTRY' 'TRANS_TYPE' 'TRANS_CORP' 'SHIP_NAME' 'TYPE_OF_LOAD'\n",
      " 'ORIGIN_PORT' 'DEST_PORT' 'G_WEIGHT' 'QUANTITY' 'UNIT_OF_QUANTITY' 'FOB'\n",
      " 'FREIGHT' 'INSURANCE' 'CIF' 'FOB_UNIT' 'TYPE_OF_PACKAGE'\n",
      " 'EXPORTER_REGION' 'PACKAGES' 'PACKAGES_DESC' 'TRANS_CORP_COUNTRY'\n",
      " 'SALE_CONDITION' 'ECONOMIC_ZONE' 'EXPORTER_ECONOMIC_KEY'\n",
      " 'TRANS_DOC_NUMBER' 'TRANS_DOC_DATE' 'VOYAGE_NUMBER' 'INCOTERMS' 'PAYMENT'\n",
      " 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:21:54,104 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:21:54,195 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/export_paths/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:21:54,295 - Data Mapping - INFO - Export data mapping and cleaning takes 0.02 minutes\n",
      "2025-08-20 13:21:54,399 - Bash Mapping - INFO - target_tab shape: (98419, 47)\n",
      "2025-08-20 13:21:54,940 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'SHIPPER_CODE' 'SHIPPER_NAME'\n",
      " 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS' 'DEPARTURE_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'DEST_COUNTRY' 'CONSIGNEE_NAME_ADDRESS'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_RECEIPT' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UOM' 'QTY'\n",
      " 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:21:55,538 - Data Mapping - INFO - Export data mapping and cleaning takes 0.04 minutes\n",
      "2025-08-20 13:21:55,700 - Bash Mapping - INFO - target_tab shape: (141342, 47)\n",
      "2025-08-20 13:21:56,773 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:21:57,100 - Data Mapping - INFO - Export data mapping and cleaning takes 0.05 minutes\n",
      "2025-08-20 13:21:57,205 - Bash Mapping - INFO - target_tab shape: (133800, 47)\n",
      "2025-08-20 13:21:57,413 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:21:57,522 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/智利/export_paths/202501-EXP.csv\n",
      "2025-08-20 13:21:58,354 - Data Mapping - INFO - imp_df columns: ['DAY' 'MONTH' 'YEAR' 'CONTROL_ID' 'CUSTOMS' 'EXPORTER_ID' 'VERIFIER'\n",
      " 'EXPORTER' 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT' 'VARIETEY' 'BRAND' 'DESC'\n",
      " 'DEST_COUNTRY' 'TRANS_TYPE' 'TRANS_CORP' 'SHIP_NAME' 'TYPE_OF_LOAD'\n",
      " 'ORIGIN_PORT' 'DEST_PORT' 'G_WEIGHT' 'QUANTITY' 'UNIT_OF_QUANTITY' 'FOB'\n",
      " 'FREIGHT' 'INSURANCE' 'CIF' 'FOB_UNIT' 'TYPE_OF_PACKAGE'\n",
      " 'EXPORTER_REGION' 'PACKAGES' 'PACKAGES_DESC' 'TRANS_CORP_COUNTRY'\n",
      " 'SALE_CONDITION' 'ECONOMIC_ZONE' 'EXPORTER_ECONOMIC_KEY'\n",
      " 'TRANS_DOC_NUMBER' 'TRANS_DOC_DATE' 'VOYAGE_NUMBER' 'INCOTERMS' 'PAYMENT'\n",
      " 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:21:58,715 - Data Mapping - INFO - Export data mapping and cleaning takes 0.02 minutes\n",
      "2025-08-20 13:21:58,843 - Bash Mapping - INFO - target_tab shape: (127496, 47)\n",
      "2025-08-20 13:22:01,359 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:22:01,444 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/export_paths/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:22:01,931 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:22:02,021 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/智利/export_paths/202503-EXP.csv\n",
      "2025-08-20 13:22:02,636 - Data Mapping - INFO - imp_df columns: ['DAY' 'MONTH' 'YEAR' 'CONTROL_ID' 'CUSTOMS' 'EXPORTER_ID' 'VERIFIER'\n",
      " 'EXPORTER' 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT' 'VARIETEY' 'BRAND' 'DESC'\n",
      " 'DEST_COUNTRY' 'TRANS_TYPE' 'TRANS_CORP' 'SHIP_NAME' 'TYPE_OF_LOAD'\n",
      " 'ORIGIN_PORT' 'DEST_PORT' 'G_WEIGHT' 'QUANTITY' 'UNIT_OF_QUANTITY' 'FOB'\n",
      " 'FREIGHT' 'INSURANCE' 'CIF' 'FOB_UNIT' 'TYPE_OF_PACKAGE'\n",
      " 'EXPORTER_REGION' 'PACKAGES' 'PACKAGES_DESC' 'TRANS_CORP_COUNTRY'\n",
      " 'SALE_CONDITION' 'ECONOMIC_ZONE' 'EXPORTER_ECONOMIC_KEY'\n",
      " 'TRANS_DOC_NUMBER' 'TRANS_DOC_DATE' 'VOYAGE_NUMBER' 'INCOTERMS' 'PAYMENT'\n",
      " 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:02,839 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:22:02,940 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/export_paths/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:22:02,950 - Data Mapping - INFO - Export data mapping and cleaning takes 0.02 minutes\n",
      "2025-08-20 13:22:03,029 - Bash Mapping - INFO - target_tab shape: (100956, 47)\n",
      "2025-08-20 13:22:03,196 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:22:03,498 - Data Mapping - INFO - Export data mapping and cleaning takes 0.03 minutes\n",
      "2025-08-20 13:22:03,621 - Bash Mapping - INFO - target_tab shape: (129629, 47)\n",
      "2025-08-20 13:22:04,851 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'SHIPPER_CODE' 'SHIPPER_NAME'\n",
      " 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS' 'DEPARTURE_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'DEST_COUNTRY' 'CONSIGNEE_NAME_ADDRESS'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_RECEIPT' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UOM' 'QTY'\n",
      " 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:05,483 - Data Mapping - INFO - Export data mapping and cleaning takes 0.04 minutes\n",
      "2025-08-20 13:22:05,667 - Bash Mapping - INFO - target_tab shape: (130121, 47)\n",
      "2025-08-20 13:22:06,710 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:22:06,821 - Bash Mapping - INFO - all data takes 0.23 minutes\n",
      "2025-08-20 13:22:06,825 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:22:07,614 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:22:07,696 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/export_paths/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:22:09,008 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:22:09,299 - Data Mapping - INFO - Export data mapping and cleaning takes 0.03 minutes\n",
      "2025-08-20 13:22:09,427 - Bash Mapping - INFO - target_tab shape: (152034, 47)\n",
      "2025-08-20 13:22:09,834 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:22:09,931 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/import_paths/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:22:10,906 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'SHIPPER_CODE' 'SHIPPER_NAME'\n",
      " 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS' 'DEPARTURE_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'DEST_COUNTRY' 'CONSIGNEE_NAME_ADDRESS'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_RECEIPT' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UOM' 'QTY'\n",
      " 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:11,444 - Data Mapping - INFO - Export data mapping and cleaning takes 0.03 minutes\n",
      "2025-08-20 13:22:11,591 - Bash Mapping - INFO - target_tab shape: (141342, 47)\n",
      "2025-08-20 13:22:12,578 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:22:12,682 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/export_paths/202502-EXP-RAW.csv\n",
      "2025-08-20 13:22:12,751 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:22:12,775 - Data Mapping - INFO - Export data mapping and cleaning takes 0.0 minutes\n",
      "2025-08-20 13:22:12,789 - Bash Mapping - INFO - target_tab shape: (7660, 47)\n",
      "2025-08-20 13:22:12,914 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:22:12,986 - Bash Mapping - INFO - all data takes 0.33 minutes\n",
      "2025-08-20 13:22:12,988 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:22:14,821 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:22:14,905 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/import_paths/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:22:15,613 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'SHIPPER_CODE' 'SHIPPER_NAME'\n",
      " 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS' 'DEPARTURE_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'DEST_COUNTRY' 'CONSIGNEE_NAME_ADDRESS'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_RECEIPT' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UOM' 'QTY'\n",
      " 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:16,123 - Data Mapping - INFO - Export data mapping and cleaning takes 0.02 minutes\n",
      "2025-08-20 13:22:16,250 - Bash Mapping - INFO - target_tab shape: (130121, 47)\n",
      "2025-08-20 13:22:18,295 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:22:18,397 - Bash Mapping - INFO - all data takes 0.42 minutes\n",
      "2025-08-20 13:22:18,398 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:22:18,399 - Data Goverance - INFO - country_normal_mapping takes 0.42195667028427125 minutes\n"
     ]
    }
   ],
   "source": [
    "# 出口跑批\n",
    "start = time.time()\n",
    "\n",
    "params_list = [([ctry], root_dir, csv_files_path, all_country_cols_dict) for ctry in country_list]\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: bash_mapping_export(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result())\n",
    "        # pass\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'country_normal_mapping takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c960344b-a014-448c-9d24-221c07d668eb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93098180-1531-43b7-87b9-3ce668e685ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "57029054-4736-4783-a7d6-3418e0346157",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "### 5.2.2 进口跑批"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "860b40df-c79a-4074-8e5b-d890fbbf2400",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 进口跑批\n",
    "# start = time.time()\n",
    "# for ctry in tqdm(country_list):\n",
    "#     logger.info(ctry)\n",
    "#     if file_tree[ctry]['import_paths']:\n",
    "#         # N = len(file_tree[ctry]['import_paths'])\n",
    "#         for i in tqdm(range(0, len(file_tree[ctry]['import_paths']))):\n",
    "#             imp_path = file_tree[ctry]['import_paths'][i]\n",
    "#             logger.info(imp_path)\n",
    "#             imp_file = str(imp_path).split(os.sep)[-1]\n",
    "#             func_map = [x for x in function_map if x.get('中文国名')==ctry]\n",
    "#             target_tab = func_map[0].get('import_fuc')(imp_path, all_country_cols_dict[ctry]['import_paths'])\n",
    "#             # 目标表保存至csv\n",
    "#             logger.info(f'target_tab shape: {target_tab.shape}')\n",
    "#             save_data_to_csv(target_tab, csv_files_path, file_name=imp_file, country_code=ctry, IE_tag='IMPORT')\n",
    "#             del target_tab\n",
    "#             gc.collect()\n",
    "# end = time.time()\n",
    "# logger.info(f'all data takes {round((end - start)/60, 2)} minutes')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "48dec95f-ee93-4b11-937c-5d7ec4fb16b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取文件目录，区分出口、进口数据\n",
    "root_dir = Path('dataset/data_20250724/20251t3_src_version')\n",
    "\n",
    "# csv结果文件保存路径\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_save')\n",
    "csv_files_path = Path('dataset/data_20250724/20251t3_csv_save2')\n",
    "# model_result1 = pickle.load(open('result/model_rslt_00_all_country_cols_dict_20250804.pickle', 'rb'))\n",
    "model_result1 = pickle.load(open('result/model_rslt_05_20250810.pickle', 'rb'))\n",
    "\n",
    "all_country_cols_dict = model_result1['all_country_cols_dict']\n",
    "\n",
    "# country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "# country_list = ['俄罗斯陆运', '肯尼亚']\n",
    "country_list = ['智利', '坦桑尼亚', '乌克兰']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "e2fe8ada-a656-4396-8ffd-c6eafb1050e4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:22:18,439 - Bash Mapping - INFO - 智利\n",
      "2025-08-20 13:22:18,439 - Bash Mapping - INFO - 坦桑尼亚\n",
      "2025-08-20 13:22:18,439 - Bash Mapping - INFO - 乌克兰\n",
      "2025-08-20 13:22:18,439 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/智利/import_paths/202501-IMP.csv\n",
      "2025-08-20 13:22:18,440 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/import_paths/202501-IMP-RAW.csv\n",
      "2025-08-20 13:22:18,440 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/import_paths/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:22:21,873 - Data Mapping - INFO - imp_df columns: ['DAY' 'MONTH' 'YEAR' 'CUSTOMS' 'CONTROL_ID' 'IMPORTER_ID' 'VERIFIER'\n",
      " 'IMPORTER' 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT' 'BRAND' 'VARIETEY' 'DESC'\n",
      " 'ORIGIN_COUNTRY' 'SALES_COUNTRY' 'TRANS_TYPE' 'PAYMENT' 'ORIGIN_PORT'\n",
      " 'DEST_PORT' 'TRANS_CORP' 'TYPE_OF_LOAD' 'TYPE_OF_PACKAGE' 'G_WEIGHT'\n",
      " 'INCOTERMS' 'TAX' 'QUANTITY' 'UNIT_OF_QUANTITY' 'FOB' 'FREIGHT'\n",
      " 'INSURANCE' 'CIF' 'CIF_UNIT' 'FOB_UNIT' 'TRANS_CORP_COUNTRY' 'US_TAX'\n",
      " 'PACKAGES' 'ECONOMIC_ZONE' 'IMPORTER_ECONOMIC_KEY' 'MANIFEST_NUMBER'\n",
      " 'MANIFEST_DATE' 'TRANS_DOC_NUMBER' 'TRANS_DOC_DATE' 'dataid'\n",
      " 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:22,820 - Data Mapping - INFO - Import data mapping and cleaning takes 0.07 minutes\n",
      "2025-08-20 13:22:23,217 - Bash Mapping - INFO - target_tab shape: (383012, 47)\n",
      "2025-08-20 13:22:27,023 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:22:27,927 - Data Mapping - INFO - Import data mapping and cleaning takes 0.16 minutes\n",
      "2025-08-20 13:22:28,394 - Bash Mapping - INFO - target_tab shape: (432559, 47)\n",
      "2025-08-20 13:22:29,610 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'CONSIGNEE_CODE'\n",
      " 'CONSIGNEE_NAME_ADDRESS' 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS'\n",
      " 'DEPARTURE_COUNTRY' 'TRADING_COUNTRY' 'ORIGIN_COUNTRY' 'SHIPPER_NAME'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_DELIVERY' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UNIT_OF_QUANTITY'\n",
      " 'QUANTITY' 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:32,345 - Data Mapping - INFO - Import data mapping and cleaning takes 0.23 minutes\n",
      "2025-08-20 13:22:33,200 - Bash Mapping - INFO - target_tab shape: (652325, 47)\n",
      "2025-08-20 13:22:37,488 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:22:37,678 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/智利/import_paths/202502-IMP.csv\n",
      "2025-08-20 13:22:40,046 - Data Mapping - INFO - imp_df columns: ['DAY' 'MONTH' 'YEAR' 'CUSTOMS' 'CONTROL_ID' 'IMPORTER_ID' 'VERIFIER'\n",
      " 'IMPORTER' 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT' 'BRAND' 'VARIETEY' 'DESC'\n",
      " 'ORIGIN_COUNTRY' 'SALES_COUNTRY' 'TRANS_TYPE' 'PAYMENT' 'ORIGIN_PORT'\n",
      " 'DEST_PORT' 'TRANS_CORP' 'TYPE_OF_LOAD' 'TYPE_OF_PACKAGE' 'G_WEIGHT'\n",
      " 'INCOTERMS' 'TAX' 'QUANTITY' 'UNIT_OF_QUANTITY' 'FOB' 'FREIGHT'\n",
      " 'INSURANCE' 'CIF' 'CIF_UNIT' 'FOB_UNIT' 'TRANS_CORP_COUNTRY' 'US_TAX'\n",
      " 'PACKAGES' 'ECONOMIC_ZONE' 'IMPORTER_ECONOMIC_KEY' 'MANIFEST_NUMBER'\n",
      " 'MANIFEST_DATE' 'TRANS_DOC_NUMBER' 'TRANS_DOC_DATE' 'dataid'\n",
      " 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:40,970 - Data Mapping - INFO - Import data mapping and cleaning takes 0.05 minutes\n",
      "2025-08-20 13:22:41,296 - Bash Mapping - INFO - target_tab shape: (354974, 47)\n",
      "2025-08-20 13:22:42,239 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:22:42,345 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/import_paths/202503-IMP-RAW.csv\n",
      "2025-08-20 13:22:46,094 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:22:46,776 - Data Mapping - INFO - Import data mapping and cleaning takes 0.07 minutes\n",
      "2025-08-20 13:22:47,151 - Bash Mapping - INFO - target_tab shape: (345735, 47)\n",
      "2025-08-20 13:22:50,048 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:22:50,163 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/智利/import_paths/202503-IMP.csv\n",
      "2025-08-20 13:22:53,143 - Data Mapping - INFO - imp_df columns: ['DAY' 'MONTH' 'YEAR' 'CUSTOMS' 'CONTROL_ID' 'IMPORTER_ID' 'VERIFIER'\n",
      " 'IMPORTER' 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT' 'BRAND' 'VARIETEY' 'DESC'\n",
      " 'ORIGIN_COUNTRY' 'SALES_COUNTRY' 'TRANS_TYPE' 'PAYMENT' 'ORIGIN_PORT'\n",
      " 'DEST_PORT' 'TRANS_CORP' 'TYPE_OF_LOAD' 'TYPE_OF_PACKAGE' 'G_WEIGHT'\n",
      " 'INCOTERMS' 'TAX' 'QUANTITY' 'UNIT_OF_QUANTITY' 'FOB' 'FREIGHT'\n",
      " 'INSURANCE' 'CIF' 'CIF_UNIT' 'FOB_UNIT' 'TRANS_CORP_COUNTRY' 'US_TAX'\n",
      " 'PACKAGES' 'ECONOMIC_ZONE' 'IMPORTER_ECONOMIC_KEY' 'MANIFEST_NUMBER'\n",
      " 'MANIFEST_DATE' 'TRANS_DOC_NUMBER' 'TRANS_DOC_DATE' 'dataid'\n",
      " 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:22:54,160 - Data Mapping - INFO - Import data mapping and cleaning takes 0.07 minutes\n",
      "2025-08-20 13:22:54,508 - Bash Mapping - INFO - target_tab shape: (385873, 47)\n",
      "2025-08-20 13:22:56,072 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:22:56,178 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/坦桑尼亚/import_paths/202502-IMP-RAW.csv\n",
      "2025-08-20 13:22:59,954 - Data Mapping - INFO - imp_df columns: ['CUSTOMS_OFFICE_CODE' 'TANSAD_YY' 'TANSAD_DATE' 'TANSAD_SERIAL_NO'\n",
      " 'TANSAD_FULL_NO' 'REGIME_NAME' 'TANSAD_YYYYMM' 'RELEASE_YYYYMM'\n",
      " 'PROCESSING_STATUS_NAME' 'ORIGIN_COUNTRY' 'CONSIGNMENT_COUNTRY'\n",
      " 'TRADING_COUNTRY' 'EXPORT_COUNTRY' 'DEST_COUNTRY' 'CRN'\n",
      " 'MANIFEST_SUBMISSION_DATE' 'BL_NO' 'IS_CONTAINERIZED' 'NO_OF_CONTAINERS'\n",
      " 'VESSEL_NAME' 'TRANSPORT_MEANS_ID' 'TRANSPORT_NATIONALITY_CD'\n",
      " 'ARRIVAL_DATE' 'DISCHARGE_DATE' 'ENTRY_OFFICE_CODE'\n",
      " 'SELECTIVITY_RESULT_CD' 'CLEARANCE_PLAN_CD' 'TRANSPORT_MODE_CODE'\n",
      " 'TRANSPORT_MODE_NAME' 'IMPORTER_TAX_ID' 'IMPORTER_NAME' 'EXPORTER_TAX_ID'\n",
      " 'EXPORTER_NAME' 'DECLARANT_TAX_ID' 'DECLARANT_NAME'\n",
      " 'ITEM_INVOICE_CARGO_VALUE_FOREIGN_CURRENCY' 'FOREIGN_CURRENCY'\n",
      " 'EXCHANGE_RATE' 'PREFERENCE_CODE' 'ITEM_NO' 'EPC_CODE' 'APC_CODE'\n",
      " 'HS_CODE' 'HS_CODE_DESC' 'PRODUCT_DESC' 'USED_YN' 'QUANTITY'\n",
      " 'UNIT_OF_MEASURE' 'TOTAL_FREIGHT_AMOUNT' 'ITEM_G_WEIGHT' 'ITEM_N_WEIGHT'\n",
      " 'ITEM_CARGO_VALUE_STATISTICS' 'ITEM_CUSTOMS_CARGO_VALUE' 'IMPORT_DUTY'\n",
      " 'IMPORT_DUTY_EXEMPTED' 'EXPORT_DUTY' 'EXPORT_DUTY_EXEMPTED' 'EXCISE_DUTY'\n",
      " 'EXCISE_DUTY_EXEMPTED' 'VAT' 'VAT_EXEMPTED' 'FUEL_LEVY' 'ELV' 'EXP'\n",
      " 'VEH_REG' 'RAILWAY_DEV_LEVY' 'CUSTOMS_PROCESS_FEE' 'DECLARED_CUSTOMS_VAL'\n",
      " 'ASSESSED_CUSTOMS_VAL' 'ITEM_TAX_AMOUNT' 'dataid' 'src_country' 'iesign'\n",
      " 'version']\n",
      "2025-08-20 13:23:00,698 - Data Mapping - INFO - Import data mapping and cleaning takes 0.08 minutes\n",
      "2025-08-20 13:23:01,084 - Bash Mapping - INFO - target_tab shape: (359832, 47)\n",
      "2025-08-20 13:23:04,538 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:23:04,654 - Bash Mapping - INFO - all data takes 0.77 minutes\n",
      "2025-08-20 13:23:04,660 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:23:07,437 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:23:07,549 - Bash Mapping - INFO - all data takes 0.82 minutes\n",
      "2025-08-20 13:23:07,550 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:23:10,841 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:23:11,023 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/import_paths/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:23:14,755 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'CONSIGNEE_CODE'\n",
      " 'CONSIGNEE_NAME_ADDRESS' 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS'\n",
      " 'DEPARTURE_COUNTRY' 'TRADING_COUNTRY' 'ORIGIN_COUNTRY' 'SHIPPER_NAME'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_DELIVERY' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UOM' 'QTY'\n",
      " 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:23:17,528 - Data Mapping - INFO - Import data mapping and cleaning takes 0.11 minutes\n",
      "2025-08-20 13:23:18,367 - Bash Mapping - INFO - target_tab shape: (771565, 47)\n",
      "2025-08-20 13:23:29,526 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:23:29,752 - Bash Mapping - INFO - dataset/data_20250724/20251t3_src_version/乌克兰/import_paths/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:23:33,070 - Data Mapping - INFO - imp_df columns: ['REG_DATE' 'CUSTOMS_OFFICE_CODE' 'DECLARATION_NUMBER' 'ITEM_NO' 'HS_CODE'\n",
      " 'PRODUCT_DESC' 'CUSTOMS_OFFICE_NAME_3WEI' 'CONSIGNEE_CODE'\n",
      " 'CONSIGNEE_NAME_ADDRESS' 'GUARANTEE_CODE' 'GUARANTEE_NAME_ADDRESS'\n",
      " 'DEPARTURE_COUNTRY' 'TRADING_COUNTRY' 'ORIGIN_COUNTRY' 'SHIPPER_NAME'\n",
      " 'CONTRACT_HOLDER_NAME' 'PLACE_OF_DELIVERY' 'INCOTERMS'\n",
      " 'TRANS_TYPE_AT_BORDER' 'TRANS_TYPE_WITHIN_COUNTRY' 'UOM' 'QTY'\n",
      " 'N_WEIGHT_IN_KG' 'INVOICE_VALUE_USD' 'CUSTOMS_VALUE_USD'\n",
      " 'DECLARATION_TYPE_CODE' 'DIRECTION' 'PREV_CUSTOMS_MODE_CODE'\n",
      " 'CUSTOMS_MODE' 'dataid' 'src_country' 'iesign' 'version']\n",
      "2025-08-20 13:23:35,394 - Data Mapping - INFO - Import data mapping and cleaning takes 0.09 minutes\n",
      "2025-08-20 13:23:36,116 - Bash Mapping - INFO - target_tab shape: (675826, 47)\n",
      "2025-08-20 13:23:48,444 - File Process - INFO - CSV data saved to dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:23:48,657 - Bash Mapping - INFO - all data takes 1.5 minutes\n",
      "2025-08-20 13:23:48,658 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:23:48,664 - Data Goverance - INFO - country_normal_mapping takes 1.5041120807329813 minutes\n"
     ]
    }
   ],
   "source": [
    "# 进口跑批\n",
    "start = time.time()\n",
    "\n",
    "params_list = [([ctry], root_dir, csv_files_path, all_country_cols_dict) for ctry in country_list]\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: bash_mapping_import(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result())\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'country_normal_mapping takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2f27e0d-c342-438c-b148-ea7e48fd5f08",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "28bcc84d-20ff-4f10-b8c5-304ed5cc36ea",
   "metadata": {},
   "source": [
    "## 5.3 乌克兰1月出口单独处理"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a64ec6c7-dc54-4a63-a8f0-1b4548860150",
   "metadata": {},
   "source": [
    "数据来源路径： 'dataset/data_20250724/2025年1-3月汇总/乌克兰/Ukraine_E_2025.01.xlsx'\n",
    "\n",
    "添加数据主键 dataid\n",
    "添加数据主键 country\n",
    "添加数据主键 iesign\n",
    "源数据提取为csv路径： 'dataset/data_20250724/2025年1-3月汇总/乌克兰/Ukraine_E_2025.01.csv'\n",
    "\n",
    "mapping结果文件保存路径： 'dataset/data_20250724/20251t3_csv_save/乌克兰/EXPORT/UA_EXPORT_202501.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "da4cff0c-5076-4ab6-a201-b890ebb1f73c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# excel_file = pd.ExcelFile('dataset/data_20250724/2025年1-3月汇总/乌克兰/Ukraine_E_2025.01.xlsx')\n",
    "# sheet_names = excel_file.sheet_names\n",
    "# file_df = pd.read_excel('dataset/data_20250724/2025年1-3月汇总/乌克兰/Ukraine_E_2025.01.xlsx', sheet_name=sheet_names[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "8c0c5d6f-dda6-49e1-b48f-f1562f8722a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# file_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "49965202-ab0b-4676-a09d-6af71f128170",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# file_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "45272e76-20e0-4e7f-87a7-49021cb31bd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ukr_df['dataid'] = [str(uuid.uuid4()).replace('-', '')[0:16] for _ in range(imp_df.shape[0])]\n",
    "# ukr_df['country'] = country36_dict['Ukraine'].get('国别编码')\n",
    "# ukr_df['iesign'] = 'E'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "491ca74a-5a64-40e7-b4ff-daf9d5f8e87e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country36_dict.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a1fced4-001f-4a7f-b93e-b8f4e4bc6a0b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "749a4c92-4de8-4f87-b634-442f4f92568d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea54260b-e422-4d03-84ba-5a718bf2856d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "965e6f87-8153-468f-a4d2-f39ea7c658dc",
   "metadata": {},
   "source": [
    "# 6 利用LLM做国家标准化"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ba87ce7-040b-4003-8506-36e289d93979",
   "metadata": {},
   "source": [
    "思路1: 单个文件中的国家名称去重后，调LLM \n",
    "\n",
    "思路2: 多个文件中的国家名称去重后，调LLM\n",
    "\n",
    "思路3: 一个国家中的所有文件合并，然后里边的国家名去重后，调LLM\n",
    "\n",
    "思路4: 多个国家中的所有文件合并，然后里边的国家名去重后，调LLM\n",
    "\n",
    "**思路5**: 所有文件中的国家名去重后，调LLM，得到文本结果，然后解析\n",
    "\n",
    "由于LLM API响应时间过长， 经过多次尝试， 思路5最可行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "054ed5c6-4bc7-4701-81d5-92d504418df9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "de0b70dc-1d9a-4059-b99b-c18db5dbb90d",
   "metadata": {},
   "source": [
    "## 6.1 利用llm获取国家映射字典-获取全部国家列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "acf464b8-1473-455e-8244-6f39c1bb2d25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['all_country_cols_dict', 'all_country_list', 'llm_country_describe', 'llm_country_mapping', 'country_mapping', 'currency_df', 'currency_on_usd'])"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_result.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "18ca2f18-60fb-4800-84ce-ff11929ba0e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "root_path = Path('dataset/data_20250724/20251t3_csv_save2')\n",
    "_, file_paths = get_dir_files(root_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "201b4dce-7cbb-40c9-a533-fb6e15561461",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-空运/EXPORT/BOL-AIR-EXPORT-202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-空运/EXPORT/BOL-AIR-EXPORT-202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-空运/EXPORT/BOL-AIR-EXPORT-202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-空运/IMPORT/BOL-AIR-IMPORT-202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-空运/IMPORT/BOL-AIR-IMPORT-202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-空运/IMPORT/BOL-AIR-IMPORT-202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/墨西哥/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/墨西哥/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/墨西哥/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/墨西哥/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/墨西哥/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/墨西哥/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/孟加拉/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/孟加拉/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/孟加拉/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥斯达黎加/EXPORT/202501-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥斯达黎加/EXPORT/202503-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥斯达黎加/EXPORT/202502-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥斯达黎加/IMPORT/202501-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥斯达黎加/IMPORT/202503-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥斯达黎加/IMPORT/202502-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴基斯坦/EXPORT/202503-EXP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴基斯坦/EXPORT/202502-EXP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴基斯坦/EXPORT/202501-EXP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴基斯坦/IMPORT/202503-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴基斯坦/IMPORT/202502-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴基斯坦/IMPORT/202501-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/科特迪瓦/EXPORT/KT_EXPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/科特迪瓦/EXPORT/KT_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/科特迪瓦/EXPORT/KT_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/科特迪瓦/IMPORT/KT_IMPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/科特迪瓦/IMPORT/KT_IMPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/科特迪瓦/IMPORT/KT_IMPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/莱索托/EXPORT/LS_EXPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/莱索托/EXPORT/LS_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/莱索托/EXPORT/LS_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/莱索托/IMPORT/LS_IMPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/莱索托/IMPORT/LS_IMPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/莱索托/IMPORT/LS_IMPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-海运/EXPORT/BOL-SEA-EXPORT-202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-海运/EXPORT/BOL-SEA-EXPORT-202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-海运/EXPORT/BOL-SEA-EXPORT-202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-海运/IMPORT/BOL-SEA-IMPORT-202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-海运/IMPORT/BOL-SEA-IMPORT-202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/秘鲁-海运/IMPORT/BOL-SEA-IMPORT-202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202502(LONG)_P6.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202502(LONG)_P4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202501(LONG)_P6.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_2025012(LONG)_P4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P5.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P7.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_2025012(LONG)_P1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202501(LONG)_P7.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202502(LONG)_P5.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202502(LONG)_P2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202502(LONG)_P3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202502(LONG)_P1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_2025012(LONG)_P5.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_2025012(LONG)_P3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P6.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_2025012(LONG)_P2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/EXPORT/VIETNAM_EXPORT_202503(LONG)_P1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_2025012(LONG)_P2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_2025012(LONG)_P3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P9.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_2025012(LONG)_P5.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P8.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P6.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_2025012(LONG)_P1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202501(LONG)_P6.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P5.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202501(LONG)_P7.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_2025012(LONG)_P4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P6.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202502(LONG)_P7.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P5.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/越南/IMPORT/VIETNAM_IMPORT_202503(LONG)_P7.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/埃塞俄比亚/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/埃塞俄比亚/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/埃塞俄比亚/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/埃塞俄比亚/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/埃塞俄比亚/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/埃塞俄比亚/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/阿根廷/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/阿根廷/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/阿根廷/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/阿根廷/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/阿根廷/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/阿根廷/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌拉圭/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌拉圭/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌拉圭/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌拉圭/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌拉圭/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌拉圭/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/厄瓜多尔/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/厄瓜多尔/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/厄瓜多尔/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/厄瓜多尔/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/厄瓜多尔/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/厄瓜多尔/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拉圭/EXPORT/202501-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拉圭/EXPORT/202503-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拉圭/EXPORT/202502-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拉圭/IMPORT/202501-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拉圭/IMPORT/202503-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拉圭/IMPORT/202502-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/纳米比亚/IMPORT/202503-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/纳米比亚/IMPORT/202502-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/纳米比亚/IMPORT/202501-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202501-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202503-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202502-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202501-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202503-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202502-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/斯里兰卡/EXPORT/202501-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/斯里兰卡/EXPORT/202503-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/斯里兰卡/EXPORT/202502-EXP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/斯里兰卡/IMPORT/202501-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/斯里兰卡/IMPORT/202503-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/斯里兰卡/IMPORT/202502-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌干达/EXPORT/UG0020_EXP_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌干达/EXPORT/UG0020_EXP_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌干达/EXPORT/UG0020_EXP_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌干达/IMPORT/UG0010_IMP_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌干达/IMPORT/UG0010_IMP_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌干达/IMPORT/UG0010_IMP_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拿马/EXPORT/202503-EXP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拿马/EXPORT/202502-EXP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拿马/EXPORT/202501-EXP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拿马/IMPORT/202503-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拿马/IMPORT/202502-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/巴拿马/IMPORT/202501-IMP-FULL.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/加纳/EXPORT/GHANA_EXPORT_RAW_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/加纳/EXPORT/GHANA_EXPORT_RAW_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/加纳/EXPORT/GHANA_EXPORT_RAW_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/加纳/IMPORT/GHANA_IMPORT_RAW_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/加纳/IMPORT/GHANA_IMPORT_RAW_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/加纳/IMPORT/GHANA_IMPORT_RAW_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/尼日利亚/EXPORT/NG_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/尼日利亚/EXPORT/NG_EXPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/尼日利亚/EXPORT/NG_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/尼日利亚/IMPORT/202501-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/尼日利亚/IMPORT/202503-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/尼日利亚/IMPORT/202502-IMP-RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/喀麦隆/IMPORT/CAMEROON_ALL_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/喀麦隆/IMPORT/CAMEROON_ALL_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/喀麦隆/IMPORT/CAMEROON_ALL_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/菲律宾/EXPORT/PH_EXPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/菲律宾/EXPORT/PH_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/菲律宾/EXPORT/PH_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/菲律宾/IMPORT/PH_IMPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/菲律宾/IMPORT/PH_IMPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/菲律宾/IMPORT/PH_IMPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/马拉维/EXPORT/MALAWI_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/马拉维/EXPORT/MALAWI_EXPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/马拉维/EXPORT/MALAWI_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/马拉维/IMPORT/MALAWI_IMPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/马拉维/IMPORT/MALAWI_IMPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/马拉维/IMPORT/MALAWI_IMPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/英国/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/英国/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/英国/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/英国/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/英国/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/英国/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/EXPORT/202502out6004.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/EXPORT/202503out6004.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/EXPORT/202501out6004.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250108-14.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250307-10_access.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250211-13.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250115-18.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250323-29.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250331-0405_access.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250311-15.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250121-0205-b.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250121-0205-a.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250225-27.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250228_access.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250106-07.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250214-17.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250221-24_access.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250317-19.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250305-06.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250218-20.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250115-20.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250320-22.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250101-04.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/美国/IMPORT/us20250206-10.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/EXPORT/INDIA_EXPORT_202503_2106B.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/EXPORT/INDIA_EXPORT_202503_2106A.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/EXPORT/INDIA_EXPORT_202502_2106.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/EXPORT/INDIA_EXPORT_202501_2106.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/IMPORT/INDIA_IMPORT_202502_2105A.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/IMPORT/INDIA_IMPORT_202502_2105B.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/IMPORT/INDIA_IMPORT_202501_2105B.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/IMPORT/INDIA_IMPORT_202501_2105A.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/IMPORT/INDIA_IMPORT_202503_2105B.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/印度/IMPORT/INDIA_IMPORT_202503_2105A.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/博兹瓦纳/EXPORT/BW_EXPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/博兹瓦纳/EXPORT/BW_EXPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/博兹瓦纳/EXPORT/BW_EXPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/博兹瓦纳/IMPORT/BW_IMPORT_202502.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/博兹瓦纳/IMPORT/BW_IMPORT_202503.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/博兹瓦纳/IMPORT/BW_IMPORT_202501.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_02_2025_EN_002.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_02_2025_EN_000.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_01_2025_EN_000.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_01_2025_EN_002.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_03_2025_EN_001.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_03_2025_EN_002.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_01_2025_EN_001.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_02_2025_EN_001.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/俄罗斯陆运/IMPORT/RGD_03_2025_EN_000.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/肯尼亚/IMPORT/KENYA_IMPORT_202503_RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/肯尼亚/IMPORT/KENYA_IMPORT_202501_RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/肯尼亚/IMPORT/KENYA_IMPORT_202502_RAW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202503-IMP-2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202501-IMP-1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202502-IMP-2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202502-IMP-4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202501-IMP-3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202502-IMP-1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202501-IMP-2.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202503-IMP-3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202503-IMP-1.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202503-IMP-4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202501-IMP-4.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哈萨克斯坦/IMPORT/202502-IMP-3.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌兹别克斯坦/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌兹别克斯坦/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌兹别克斯坦/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌兹别克斯坦/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌兹别克斯坦/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/乌兹别克斯坦/IMPORT/202503-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥伦比亚/EXPORT/202501-EXP-NEW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥伦比亚/EXPORT/202502-EXP-NEW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥伦比亚/EXPORT/202503-EXP-NEW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥伦比亚/IMPORT/202503-IMP-NEW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥伦比亚/IMPORT/202501-IMP-NEW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/哥伦比亚/IMPORT/202502-IMP-NEW.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202502-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202501-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202503-EXP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202501-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202502-IMP.csv'),\n",
       " PosixPath('dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202503-IMP.csv')]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "010f8e55-38cc-405e-a6cf-a957930fa887",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "277"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get_all_data_countries=get_all_data_countries\n",
    "len(file_paths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "e3261f0f-9c89-4040-b3ff-c7030b8172ef",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# start = time.time()\n",
    "\n",
    "# params_list = [([x],) for x in file_paths]\n",
    "# results = []\n",
    "# with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "#     # 提交多个任务\n",
    "#     futures = [\n",
    "#         executor.submit(lambda p: get_all_data_countries(*p), params)\n",
    "#         for params in params_list\n",
    "#     ]\n",
    "#     # 按完成顺序获取结果\n",
    "#     for future in as_completed(futures):\n",
    "#         res = future.result()\n",
    "#         results.append(res)\n",
    "#         logger.info(f'future.result: {len(res)}')\n",
    "\n",
    "# cntry_lst_tmp = [x for r in results for x in r]\n",
    "# all_data_country_list = list(set(cntry_lst_tmp))\n",
    "# print(len(all_data_country_list))\n",
    "# model_result['all_country_list'] = all_data_country_list\n",
    "# pickle.dump(model_result, open('result/model_rslt_01.pickle', 'wb'))\n",
    "\n",
    "# end = time.time()\n",
    "# logger.info(f'country_normal_mapping takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "bebf4fb2-384b-4e6b-9de6-fe0df1453b8e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# len(all_data_country_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "aaf79cc7-1383-469d-9730-54919067a7ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 单线程\n",
    "# # 获取数据中所有国家列表\n",
    "# all_data_country_list = get_all_data_countries(file_paths)\n",
    "# print(len(all_data_country_list))\n",
    "# model_result['all_country_list'] = all_data_country_list\n",
    "# pickle.dump(model_result, open('result/model_rslt_01.pickle', 'wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b33baedc-46b2-4322-bd2c-d2741efd94dc",
   "metadata": {},
   "source": [
    "## 6.2 LLM多语种国名翻译"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03f5436a-d6b6-4e9b-b5e2-fcb2df0675ca",
   "metadata": {},
   "source": [
    "### 6.2.1 利用LLM得到映射"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "f36a96ae-9a2a-4ca7-968f-98a0b4560fae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result = pickle.load(open('result/model_rslt_01_20250810.pickle', 'rb'))\n",
    "model_result = pickle.load(open('result/model_rslt_01.pickle', 'rb'))\n",
    "all_data_country_list = model_result['all_country_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "47a11286-3d92-40b0-8a4f-37169e1eec0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1886"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_data_country_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "04dd6fe5-5497-4167-8a34-e2f4956077c9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 轻易不要跑，比较慢 - 需要半小时\n",
    "# start = time.time()\n",
    "# batch=20\n",
    "# step=5\n",
    "# STP = batch*step\n",
    "# all_country_list_2d = [all_data_country_list[i:i+STP] for i in range(0, len(all_data_country_list), STP)]\n",
    "# logger.info(f'all_country_list_2d length : {len(all_country_list_2d)}')\n",
    "\n",
    "# params_list = [(x, country_code_dict, batch, step) for x in all_country_list_2d]\n",
    "\n",
    "# results = []\n",
    "# with ThreadPoolExecutor(max_workers=20) as executor:\n",
    "#     # 提交多个任务\n",
    "#     futures = [\n",
    "#         executor.submit(lambda p: get_llm_country_normal2(*p), params)\n",
    "#         for params in params_list\n",
    "#     ]\n",
    "#     # 按完成顺序获取结果\n",
    "#     for future in as_completed(futures):\n",
    "#         res = future.result()\n",
    "#         logger.info(f'future.result: {len(res)}')\n",
    "#         results.append(res)\n",
    "    \n",
    "# llm_country_describe = [''.join(x) for r in results for x in r]\n",
    "# end = time.time()\n",
    "# logger.info(f'country_normal_mapping takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "11194451-3c70-49de-a0c6-cc78a1d89362",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# len(llm_country_describe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cdeb2cc-6a75-4ad1-8513-087a9a5ab2d2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "01b85d7a-4ab7-4894-877a-e7a3df0492c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import re\n",
    "# import json\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "412afd36-0601-4a04-9ea5-09e710014c72",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffa32f32-83f7-418f-9038-727b59ab11e7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "d7082c7b-52b5-45e0-801d-da9bfcdb9b22",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 单线程\n",
    "# llm_country_describe=get_llm_country_normal2(all_data_country_list, batch=20, step=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "84434191-3494-4808-9c97-b6749d708bd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(len(llm_country_describe))\n",
    "\n",
    "# model_result['llm_country_describe'] = llm_country_describe\n",
    "# pickle.dump(model_result, open('result/model_rslt_02.pickle', 'wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18f1a3d9-9aa4-490d-91c8-3c028f05ba1b",
   "metadata": {},
   "source": [
    "### 6.2.2 LLM结果解析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "073c5459-fad7-4b00-b84f-95d0337aa4e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result = pickle.load(open('result/model_rslt_02_20250810.pickle', 'rb'))\n",
    "# llm_country_describe1 = model_result['llm_country_describe']\n",
    "# all_data_country_list = model_result['all_country_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "dddc7ff2-facf-4196-ae35-33472ce889c9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# len(llm_country_describe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "374f6834-0f04-4370-9bfd-f64f2a876da5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# def parse2(text):\n",
    "#     json_blocks = re.findall(r'```json\\s*(\\[[\\s\\S]*?\\])\\s*```', text)\n",
    "#     cleaned_text = json_blocks[0]\n",
    "#     cleaned_text = cleaned_text.replace('\\\\xad', '\\\\u00ad').replace('\\\\xed', '\\\\u00ed')\n",
    "#     cleaned_text = cleaned_text.replace('\\\\xa0', ' ').replace('原数据', '原始国名')\n",
    "#     cleaned_text = cleaned_text.replace('中英文国名', '中文国名').replace('english国名', '英文国名')\n",
    "#     result = json.loads(cleaned_text)\n",
    "#     return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "5df18650-68e8-477b-85d9-b2888e8d6408",
   "metadata": {},
   "outputs": [],
   "source": [
    "# parse2(llm_country_describe[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "ba41de9a-a4fa-43a4-a031-3814de4777b9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# llm_country_describe[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "9e609488-ac4a-4f51-90df-7d2d8dceba26",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 解析LLM结果\n",
    "# llm_country_mapping = []\n",
    "# for i in range(len(llm_country_describe)):\n",
    "#     # source_txt = ''.join(llm_country_describe[i])\n",
    "#     print(i)\n",
    "#     if llm_country_describe[i] != '[]':\n",
    "#         source_txt = llm_country_describe[i]\n",
    "#         llm_country_mapping+=parse2(source_txt)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d660e5ff-71d8-4c97-9774-aa1947d967e7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e9e6c52-3ee1-4915-8b97-476cf5bc7cdc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "6528da83-0cf3-4e3a-a309-998f56d5ba91",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "成功解析 JSON，共 4152 条记录\n"
     ]
    }
   ],
   "source": [
    "def clean_json_text(text):\n",
    "    # 替换常见的非法转义字符\n",
    "    text = re.sub(r'\\\\(?![\\\"\\\\/bfnrt])', r'\\\\\\\\', text)\n",
    "    return text\n",
    "\n",
    "with open('ctry_dict_20250810.log', 'r', encoding='utf-8') as f:\n",
    "    raw = f.read()\n",
    "\n",
    "cleaned = clean_json_text(raw)\n",
    "\n",
    "try:\n",
    "    tmp = json.loads(cleaned)\n",
    "    print(\"成功解析 JSON，共 {} 条记录\".format(len(tmp)))\n",
    "except json.JSONDecodeError as e:\n",
    "    print(\"解析失败:\", e)\n",
    "\n",
    "tmp1 = pd.DataFrame(tmp).drop_duplicates()\n",
    "tmp1.reset_index(drop=True, inplace=True)\n",
    "llm_country_mapping = tmp1.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "fb4bfabd-b1d2-4c9d-bcdc-b40980ce7c74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1885"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(llm_country_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "8eaf405b-475e-41de-8d9e-007a462648f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_mapping = merge_country_info(all_data_country_list, llm_country_mapping, country_code)\n",
    "\n",
    "model_result['llm_country_mapping'] = llm_country_mapping\n",
    "model_result['country_mapping'] = country_mapping\n",
    "pickle.dump(model_result, open('result/model_rslt_03.pickle', 'wb'))\n",
    "# country_mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b141e499-a955-4b9c-9f18-c33fceb7c5ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "711e6878-74bf-4706-9642-8f8301de9ad0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# country_mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "0affb384-9e7d-4169-acc4-eaa8ebfc463c",
   "metadata": {},
   "outputs": [],
   "source": [
    "model_result = pickle.load(open('result/model_rslt_03_20250810.pickle', 'rb'))\n",
    "llm_country_mapping = model_result['llm_country_mapping']\n",
    "all_data_country_list = model_result['all_country_list']\n",
    "country_mapping = model_result['country_mapping']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "23dbb3cf-993f-4731-9c29-7d25b091cf9b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# len(country_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "bbb8daa7-b697-4fee-8dba-9530a367d085",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# country_mapping['SOUTH AFRICA']\n",
    "# country_mapping"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "856d7df7-6700-4ba0-8e8c-847dbf4ba15c",
   "metadata": {},
   "source": [
    "## 6.2 国名替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "05543fd3-28a2-42d9-8d3b-dd29b4ec222e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "\n",
    "country_list = ['智利', '坦桑尼亚', '乌克兰']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "c7f98279-5124-45b0-82af-9a0f4d94d23c",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_files_path = Path('dataset/data_20250724/20251t3_csv_save2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "dd2117d8-fe8e-484d-85a2-b00024a24b97",
   "metadata": {},
   "outputs": [],
   "source": [
    "pth_change_tag='20251t3_csv_correct2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "23e53224-2125-43a0-b0bb-2c4c2d5f2626",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:23:48,828 - Data Goverance - INFO - country recore count is 1886\n",
      "2025-08-20 13:23:48,828 - Data Goverance - INFO - country_mapping count is 1889\n"
     ]
    }
   ],
   "source": [
    "model_result=pickle.load(open('result/model_rslt_05_20250810.pickle', 'rb'))\n",
    "country_mapping = model_result['country_mapping']\n",
    "all_country_list = model_result['all_country_list']\n",
    "logger.info(f'country recore count is {len(all_country_list)}')\n",
    "logger.info(f'country_mapping count is {len(country_mapping)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "a2604f8c-2076-4510-be25-f2e9f05a392f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(all_country_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "6730b058-60dc-494d-8b98-d237061dcbb6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# country_mapping['South Africa'].get('国别编码')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "595b49e9-0c7d-4ed1-b816-752bdbc5f8ec",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:23:48,844 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:23:48,845 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:23:48,845 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:23:49,269 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:23:49,269 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:23:49,983 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:23:50,039 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:23:53,653 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:23:53,659 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:23:56,453 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:23:56,459 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:23:58,120 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:23:58,123 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:24:00,238 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:24:00,244 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:24:01,318 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:24:01,359 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:24:01,387 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:24:01,420 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:24:09,028 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:24:09,031 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:24:09,323 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:24:09,326 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:24:10,079 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:24:10,084 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:24:19,169 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:24:19,171 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:24:28,969 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:24:28,977 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:24:36,274 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:24:36,283 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:24:40,539 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:24:40,542 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:24:50,828 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:24:50,830 - File Process - INFO - Correct country takes 62 seconds\n",
      "2025-08-20 13:24:50,884 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:24:51,317 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:24:51,319 - File Process - INFO - Correct country takes 62 seconds\n",
      "2025-08-20 13:24:51,365 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:24:58,701 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:24:58,702 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:25:16,982 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:25:16,982 - File Process - INFO - dataset/data_20250724/20251t3_csv_save2/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:25:31,941 - File Process - INFO - correct country saved to dataset/data_20250724/20251t3_csv_correct2/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:25:31,942 - File Process - INFO - Correct country takes 103 seconds\n",
      "2025-08-20 13:25:32,045 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:25:32,045 - Data Goverance - INFO - country_normal_mapping takes 1.7200435400009155 minutes\n"
     ]
    }
   ],
   "source": [
    "# 更新国家 - 多线程\n",
    "start = time.time()\n",
    "\n",
    "params_list = [(country_mapping, [ctry], csv_files_path, pth_change_tag) for ctry in country_list]\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: country_normal_mapping(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result())\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'country_normal_mapping takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "cf8c4ff1-a093-4a03-861d-aec9be1f4df0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 更新国家 - 单线程\n",
    "# country_normal_mapping(country_mapping, country_list, csv_files_path, pth_change_tag='20251t3_csv_correct')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7260d38-389a-4cee-9869-84eb787be0a2",
   "metadata": {},
   "source": [
    "# 7 货币单位校正"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "312ad0ca-4bdc-4396-a5d6-5c7dd44c030f",
   "metadata": {},
   "source": [
    "## 7.1 利用世界银行python包wbgapi获取全世界年度货币汇率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "bba9001f-0215-48bd-9017-9e157d4bc300",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_df = get_currency_exchange_rate(2023,2025)\n",
    "# model_result['currency_df'] = currency_df\n",
    "# pickle.dump(model_result, open('result/model_rslt_04.pickle', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "05c1f305-5ac0-4656-9393-16d967736484",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# model_result = pickle.load(open('result/model_rslt_04_20250810.pickle', 'rb'))\n",
    "# currency_df = model_result['currency_df']\n",
    "# currency_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "074cb324-69f9-4609-8e20-f2eba402c8df",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_df.loc[currency_df['id']=='ARG',:]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bd5fdf6-9b35-44fe-8a73-d40a4d822e7d",
   "metadata": {},
   "source": [
    "## 7.2 获取各国货币编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "4de421e1-1afe-4361-ab0e-0d66bc123a33",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "6fda50d6-493b-4870-9f74-ff73e0ff01d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from_path = Path('dataset/data_20250724/20251t3_csv_correct2')\n",
    "# currency_cols = ['cif_currency', 'fob_currency']\n",
    "# currency_list = get_currency_list(country_list, from_path, currency_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "884cdea8-bfe4-48e9-aa8a-22ac9d7d2954",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# len(currency_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "d92f53e5-83c1-4b84-80fb-5f8580a14105",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # 将currency_list粘贴给大模型，“以上是哪国货币，其对应的国家英文名称和三位ISO国家编码是多少，请给出对应列表”\n",
    "# currency_list_df = pd.read_csv('货币编码_20250807.csv',encoding='utf-8',header=0)\n",
    "# currency_list_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "d629209d-159c-4ad6-80b7-3f04957dab66",
   "metadata": {},
   "outputs": [],
   "source": [
    "# currency_df\n",
    "# currency_df.loc[currency_df['id']=='NAM', :]\n",
    "# currency_df.loc[currency_df['id']=='ARG', :]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "95f2a472-b152-4303-b94a-2e96e0464bda",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_on_usd_df = pd.merge(currency_list_df.loc[:, ['货币代码', 'ISO国家编码']], currency_df.loc[:, ['id', 'exchange_rate_on_usd']]\n",
    "#                               , left_on='ISO国家编码', right_on='id', how='left')\n",
    "# # currency_on_usd_df\n",
    "# currency_on_usd_df.loc[currency_on_usd_df['货币代码']=='TWD', 'exchange_rate_on_usd']=29.3920\n",
    "# currency_on_usd_df.loc[currency_on_usd_df['货币代码']=='EUR', 'exchange_rate_on_usd']=0.8574\n",
    "# # currency_on_usd_df.loc[currency_on_usd_df['货币代码']=='IND', 'exchange_rate_on_usd']=currency_on_usd_df.loc[currency_on_usd_df['货币代码']=='INR', 'exchange_rate_on_usd'].values\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "8fe8bd5b-e52e-4294-89d7-541f9b32a94e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_on_usd_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "f6b3ab26-03fa-4873-845b-e0cc7f7579e2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_on_usd_df.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "ebd182ad-5795-482d-a2fc-5953f8c806da",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_on_usd=dict()\n",
    "# for x in currency_on_usd_df.values:\n",
    "#     currency_on_usd[x[0]]=round(x[3],4)\n",
    "\n",
    "# for x in currency_df.loc[:, ['id', 'exchange_rate_on_usd']].values:\n",
    "#     currency_on_usd[x[0]]=x[1]\n",
    "\n",
    "# # currency_on_usd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "9d656be7-ed4a-4108-a0b4-823e71be9db9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# currency_on_usd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "a1a3d1a3-8e9d-4859-8b85-da25818fc073",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result['currency_on_usd'] = currency_on_usd\n",
    "# pickle.dump(model_result, open('result/model_rslt_05.pickle', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "8b3a1834-7112-4374-a996-c3b2414adcf0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# model_result.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "2084ab7b-15a6-49a4-b7b4-0e1c3ffa8516",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# currency_on_usd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65f9a2de-599f-4254-bb9f-147cb5a98e6a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "1272b406-efa7-4ab6-a119-3782dd69c40c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# currency_on_usd['IND']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ad9a8c9-7fa3-4e36-a246-251a9963494d",
   "metadata": {},
   "source": [
    "## 7.3 替换货币"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "0c31909a-7a50-49d4-bd05-1d74b500d9a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "model_result1 = pickle.load(open('result/model_rslt_05_20250810.pickle', 'rb'))\n",
    "currency_on_usd = model_result1['currency_on_usd']\n",
    "# currency_on_usd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "b5e6e228-1659-402b-a412-51c9d58bd4fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "currency_on_usd_dict=currency_on_usd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40567fdd-b8a6-42f2-b8f3-8187ff4f0d39",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "5f2443fd-3149-403c-8cc5-c5b1c6d6806f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from_dir=Path('dataset/data_20250724/20251t3_csv_correct2')\n",
    "to_dir=Path('dataset/data_20250724/20251t3_csv_correct3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "4256e304-0aad-434e-b472-6920160036eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "\n",
    "# country_list = [ '阿根廷']\n",
    "country_list = ['智利', '坦桑尼亚', '乌克兰']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "545b8a69-505f-41b5-aa95-9539bdf1b3ac",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# replace_currency_to_usd(country_list, currency_on_usd_dict, from_dir, to_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "a33676ca-f163-448f-9cf7-4ccb502d1f9b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:25:32,134 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:25:32,135 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:25:32,136 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:25:32,525 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:25:32,525 - File Process - INFO - currency transform done, the data shape is (9990, 47)\n",
      "2025-08-20 13:25:32,526 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:25:33,038 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:25:33,058 - File Process - INFO - currency transform done, the data shape is (7761, 47)\n",
      "2025-08-20 13:25:33,063 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:25:36,152 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:25:36,153 - File Process - INFO - currency transform done, the data shape is (98419, 47)\n",
      "2025-08-20 13:25:36,155 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:25:38,392 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:25:38,395 - File Process - INFO - currency transform done, the data shape is (133800, 47)\n",
      "2025-08-20 13:25:38,396 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:25:40,480 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:25:40,483 - File Process - INFO - currency transform done, the data shape is (127496, 47)\n",
      "2025-08-20 13:25:40,484 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:25:41,952 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:25:41,954 - File Process - INFO - currency transform done, the data shape is (141342, 47)\n",
      "2025-08-20 13:25:41,958 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:25:42,649 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:25:42,652 - File Process - INFO - currency transform done, the data shape is (129629, 47)\n",
      "2025-08-20 13:25:42,668 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:25:43,630 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:25:43,642 - File Process - INFO - currency transform done, the data shape is (100956, 47)\n",
      "2025-08-20 13:25:43,660 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:25:49,535 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:25:49,537 - File Process - INFO - currency transform done, the data shape is (152034, 47)\n",
      "2025-08-20 13:25:49,540 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:25:49,841 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:25:49,843 - File Process - INFO - currency transform done, the data shape is (7660, 47)\n",
      "2025-08-20 13:25:49,844 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:25:50,401 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:25:50,402 - File Process - INFO - currency transform done, the data shape is (130121, 47)\n",
      "2025-08-20 13:25:50,406 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:26:02,488 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:26:02,489 - File Process - INFO - currency transform done, the data shape is (383012, 47)\n",
      "2025-08-20 13:26:02,492 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:26:05,306 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:26:05,308 - File Process - INFO - currency transform done, the data shape is (432559, 47)\n",
      "2025-08-20 13:26:05,309 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:26:15,133 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:26:15,134 - File Process - INFO - currency transform done, the data shape is (354974, 47)\n",
      "2025-08-20 13:26:15,135 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:26:15,268 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:26:15,284 - File Process - INFO - currency transform done, the data shape is (345735, 47)\n",
      "2025-08-20 13:26:15,286 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:26:27,286 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:26:27,287 - File Process - INFO - currency transform done, the data shape is (359832, 47)\n",
      "2025-08-20 13:26:27,288 - File Process - INFO - replace currency to USD takes 55 seconds\n",
      "2025-08-20 13:26:27,332 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:26:28,644 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:26:28,645 - File Process - INFO - currency transform done, the data shape is (385873, 47)\n",
      "2025-08-20 13:26:28,646 - File Process - INFO - replace currency to USD takes 57 seconds\n",
      "2025-08-20 13:26:28,690 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:26:35,033 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:26:35,033 - File Process - INFO - currency transform done, the data shape is (652325, 47)\n",
      "2025-08-20 13:26:35,034 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:26:55,167 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:26:55,168 - File Process - INFO - currency transform done, the data shape is (771565, 47)\n",
      "2025-08-20 13:26:55,168 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct2/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:27:10,226 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:27:10,226 - File Process - INFO - currency transform done, the data shape is (675826, 47)\n",
      "2025-08-20 13:27:10,227 - File Process - INFO - replace currency to USD takes 98 seconds\n",
      "2025-08-20 13:27:10,311 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:27:10,314 - Data Goverance - INFO - replace_currency_to_usd takes 1.6363520542780559 minutes\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "params_list = [([ctry], currency_on_usd_dict, from_dir, to_dir) for ctry in country_list]\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: replace_currency_to_usd(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result())\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'replace_currency_to_usd takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b5ef8b8-72ad-4f92-8d1b-c9b238f7e79d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a7c43cfa-c44f-4495-9c27-7838f6ef706e",
   "metadata": {},
   "source": [
    "## 7.4 结果检查"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "4fbbbddc-4a16-431f-8075-c5f3d8b201ab",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# !head -3 dataset/data_20250724/20251t3_csv_correct3/印度/EXPORT/INDIA_EXPORT_202501_2106.csv\n",
    "# !head -10 dataset/data_20250724/20251t3_csv_correct3/纳米比亚/IMPORT/202501-IMP-FULL.csv\n",
    "# !head -10 dataset/data_20250724/20251t3_csv_correct3/纳米比亚/IMPORT/202501-IMP-FULL.csv\n",
    "# !head -10 dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-EXP-RAW.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "52a03cba-5b36-491c-8db8-846680f20882",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# # df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202501-IMP.csv')\n",
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct3/菲律宾/IMPORT/PH_IMPORT_202501.csv')\n",
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct3/纳米比亚/IMPORT/202501-IMP-FULL.csv')\n",
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_save2/纳米比亚/IMPORT/202501-IMP-FULL.csv')\n",
    "\n",
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct3/科特迪瓦/IMPORT/KT_IMPORT_202501.csv')\n",
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct3/印度/IMPORT/INDIA_IMPORT_202501_2105A.csv')\n",
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct3/印度/EXPORT/INDIA_EXPORT_202501_2106.csv')\n",
    "\n",
    "# df.iloc[:, 0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "c5a237c6-8763-4121-b137-0800f077b238",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# df.iloc[:, 20:40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "97a4f046-c45f-4077-bf2c-af908686743a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# df.iloc[:, 40:60]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "0b3f5b95-f55f-45be-bfd7-ab25aaeaa098",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df['totalcifvalue'].sum()/100000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "454a2252-6ec7-4c6d-91c0-61417ed7c7d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df['totalfobvalue'].sum()/100000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a8f633a-8bb6-4473-9c62-d59120941b7d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9ffd3381-3ff0-4ab0-8ada-ac2ecfc5d493",
   "metadata": {},
   "source": [
    "## 7.5 特殊处理-坦桑尼亚"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "161795a7-0e9a-4fe5-b3cd-2b80a730534e",
   "metadata": {},
   "outputs": [],
   "source": [
    "transtype_mapping_dict = {\n",
    "    'AEREO': 'AIR',  # 西班牙语\"航空\"，匹配 AIR（航空运输）\n",
    "    'AIR TRANSPORT': 'AIR',  # 英语\"航空运输\"，直接匹配 AIR\n",
    "    'CARRETERO': 'ROAD',  # 西班牙语\"公路\"，匹配 ROAD（公路运输）\n",
    "    'INLAND WATERWAYS TRANSPORT': 'INLAND WATER',  # 英语\"内河运输\"，直接匹配 INLAND WATER（内河运输）\n",
    "    'MARITIMA': 'SEA',  # 西班牙语\"海洋\"，匹配 SEA（海洋运输）\n",
    "    'MULTIMODAL TRANSPORT': 'MULTI-MODAL',  # 英语\"多式联运\"，直接匹配 MULTI-MODAL（多式联运）\n",
    "    'OLEODUCTOS': 'PIPELINE',  # 西班牙语\"管道\"，匹配 PIPELINE（管道运输）\n",
    "    'OTRO': 'OTHERS',  # 西班牙语\"其他\"，匹配 OTHERS（其他）\n",
    "    'OTROS': 'OTHERS',  # 西班牙语\"其他\"（复数），匹配 OTHERS\n",
    "    'POSTAL TRANSPORT': 'MAIL',  # 英语\"邮政运输\"，直接匹配 MAIL（邮政运输）\n",
    "    'RAIL TRANSPORT': 'RAILWAY',  # 英语\"铁路运输\"，直接匹配 RAILWAY（铁路运输）\n",
    "    'ROAD TRANSPORT': 'ROAD',  # 英语\"公路运输\"，直接匹配 ROAD\n",
    "    'SEA TRANSPORT': 'SEA',  # 英语\"海洋运输\"，直接匹配 SEA\n",
    "    'TENDIDO ELECTRICO (AEREO,SUBT)': 'OTHERS',  # 西班牙语\"电力线路（空中、地下）\"，不属于标准货物运输方式，归入 OTHERS\n",
    "    'TRANSPORT ON FIXED INSTALLATION': 'PIPELINE',  # 英语\"固定设施上的运输\"，部分匹配 PIPELINE（管道是固定设施的一种）\n",
    "    'UNKNOWN': 'OTHERS',  # 英语\"未知\"，匹配 OTHERS\n",
    "    'АВТОТРАНСПОРТ СВОЇМ ХОДОМ ЯК ТОВАР': 'ROAD',  # 乌克兰语\"作为货物运输的汽车（用自己的动力）\"，匹配 ROAD（公路运输）\n",
    "    'ВАНТАЖНИЙ АВТОМОБІЛЬ': 'ROAD',  # 乌克兰语\"卡车\"，匹配 ROAD\n",
    "    'ВАНТАЖНИЙ АВТОМОБІЛЬ НА МОРСЬКОМУ СУДНІ': 'SEA',  # 乌克兰语\"在船上的卡车\"，运输方式为海运，匹配 SEA\n",
    "    'ЗАЛІЗНИЧНИЙ ВАГОН': 'RAILWAY',  # 乌克兰语\"铁路车厢\"，匹配 RAILWAY\n",
    "    'КОНТЕЙНЕР НА ВАНТАЖНОМУ АВТОМОБІЛІ': 'ROAD',  # 乌克兰语\"在卡车上的集装箱\"，运输方式为公路，匹配 ROAD\n",
    "    'КОНТЕЙНЕР НА ЗАЛІЗНИЧНОМУ ВАГОНІ': 'RAILWAY',  # 乌克兰语\"在铁路车厢上的集装箱\"，匹配 RAILWAY\n",
    "    'КОНТЕЙНЕР НА МОРСЬКОМУ СУДНІ': 'SEA',  # 乌克兰语\"在船上的集装箱\"，匹配 SEA\n",
    "    'МОРСЬКЕ СУДНО': 'SEA',  # 乌克兰语\"海船\"，匹配 SEA\n",
    "    'НЕВІДОМИЙ': 'OTHERS',  # 乌克兰语\"未知\"，匹配 OTHERS\n",
    "    'ПОШТОВЕ ВІДПРАВЛЕННЯ АВТОТРАНСПОРТОМ': 'MAIL',  # 乌克兰语\"汽车运输的邮政发送\"，运输类型为邮政，匹配 MAIL\n",
    "    'ТРУБОПРОВІДНИЙ ТРАНСПОРТ': 'PIPELINE'  # 乌克兰语\"管道运输\"，直接匹配 PIPELINE\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "2d18b99e-ea0a-43fb-86b0-c2ae20dc922c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from_dir=Path('dataset/data_20250724/20251t3_csv_correct3')\n",
    "to_dir=Path('dataset/data_20250724/20251t3_csv_correct3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "a820d67f-3a93-43f6-b7f8-86e8ca826d4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_list = ['智利', '坦桑尼亚', '乌克兰']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfa9f7a1-483b-4583-a447-767b62b01ee3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "c6af1ae7-1f30-484b-b354-2fdd3a85d5f5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# transtype_list = get_transtype_list(country_list, from_dir, transtype_cols = ['transportterm'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "77584ed5-e497-4238-85f1-fb2bfb24400d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# replace_transtype(country_list, transtype_mapping_dict, from_dir, to_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "74586eca-da82-49d0-a33d-09492b8510a1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-08-20 13:27:10,363 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:27:10,364 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:27:10,364 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:27:10,750 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-EXP-RAW.csv\n",
      "2025-08-20 13:27:10,750 - File Process - INFO - transtype mapping done, the data shape is (9990, 45)\n",
      "2025-08-20 13:27:10,751 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:27:11,289 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202503-EXP-RAW.csv\n",
      "2025-08-20 13:27:11,291 - File Process - INFO - transtype mapping done, the data shape is (7761, 45)\n",
      "2025-08-20 13:27:11,291 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:27:14,475 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202502-EXP.csv\n",
      "2025-08-20 13:27:14,477 - File Process - INFO - transtype mapping done, the data shape is (98419, 45)\n",
      "2025-08-20 13:27:14,482 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:27:16,986 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202503-TRANS-RAW.csv\n",
      "2025-08-20 13:27:16,988 - File Process - INFO - transtype mapping done, the data shape is (133800, 45)\n",
      "2025-08-20 13:27:16,990 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:27:17,751 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202501-EXP.csv\n",
      "2025-08-20 13:27:17,806 - File Process - INFO - transtype mapping done, the data shape is (127496, 45)\n",
      "2025-08-20 13:27:17,896 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:27:18,756 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/EXPORT/UA_EXPORT_202503.csv\n",
      "2025-08-20 13:27:18,757 - File Process - INFO - transtype mapping done, the data shape is (141342, 45)\n",
      "2025-08-20 13:27:18,758 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:27:21,381 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/EXPORT/202503-EXP.csv\n",
      "2025-08-20 13:27:21,387 - File Process - INFO - transtype mapping done, the data shape is (100956, 45)\n",
      "2025-08-20 13:27:21,389 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:27:21,945 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202502-TRANS-RAW.csv\n",
      "2025-08-20 13:27:21,946 - File Process - INFO - transtype mapping done, the data shape is (129629, 45)\n",
      "2025-08-20 13:27:21,947 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:27:26,830 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/EXPORT/UA_EXPORT_202502.csv\n",
      "2025-08-20 13:27:26,833 - File Process - INFO - transtype mapping done, the data shape is (130121, 45)\n",
      "2025-08-20 13:27:26,834 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:27:27,883 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202501-TRANS-RAW.csv\n",
      "2025-08-20 13:27:27,883 - File Process - INFO - transtype mapping done, the data shape is (152034, 45)\n",
      "2025-08-20 13:27:27,884 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:27:28,320 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/EXPORT/202502-EXP-RAW.csv\n",
      "2025-08-20 13:27:28,325 - File Process - INFO - transtype mapping done, the data shape is (7660, 45)\n",
      "2025-08-20 13:27:28,326 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:27:38,575 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202501-IMP.csv\n",
      "2025-08-20 13:27:38,576 - File Process - INFO - transtype mapping done, the data shape is (383012, 45)\n",
      "2025-08-20 13:27:38,580 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:27:44,218 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202501-IMP-RAW.csv\n",
      "2025-08-20 13:27:44,221 - File Process - INFO - transtype mapping done, the data shape is (432559, 45)\n",
      "2025-08-20 13:27:44,222 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:27:50,287 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202502-IMP.csv\n",
      "2025-08-20 13:27:50,289 - File Process - INFO - transtype mapping done, the data shape is (354974, 45)\n",
      "2025-08-20 13:27:50,291 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:27:53,635 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202503-IMP-RAW.csv\n",
      "2025-08-20 13:27:53,636 - File Process - INFO - transtype mapping done, the data shape is (345735, 45)\n",
      "2025-08-20 13:27:53,638 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:28:03,093 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/智利/IMPORT/202503-IMP.csv\n",
      "2025-08-20 13:28:03,094 - File Process - INFO - transtype mapping done, the data shape is (385873, 45)\n",
      "2025-08-20 13:28:03,096 - File Process - INFO - transtype mapping takes 53 seconds\n",
      "2025-08-20 13:28:03,140 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:28:03,354 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/坦桑尼亚/IMPORT/202502-IMP-RAW.csv\n",
      "2025-08-20 13:28:03,356 - File Process - INFO - transtype mapping done, the data shape is (359832, 45)\n",
      "2025-08-20 13:28:03,356 - File Process - INFO - transtype mapping takes 53 seconds\n",
      "2025-08-20 13:28:03,395 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:28:05,618 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202501.csv\n",
      "2025-08-20 13:28:05,618 - File Process - INFO - transtype mapping done, the data shape is (652325, 45)\n",
      "2025-08-20 13:28:05,619 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:28:21,093 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202503.csv\n",
      "2025-08-20 13:28:21,094 - File Process - INFO - transtype mapping done, the data shape is (771565, 45)\n",
      "2025-08-20 13:28:21,094 - File Process - INFO - the path : dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:28:34,560 - File Process - INFO - save path: dataset/data_20250724/20251t3_csv_correct3/乌克兰/IMPORT/UA_IMPORT_202502.csv\n",
      "2025-08-20 13:28:34,560 - File Process - INFO - transtype mapping done, the data shape is (675826, 45)\n",
      "2025-08-20 13:28:34,561 - File Process - INFO - transtype mapping takes 84 seconds\n",
      "2025-08-20 13:28:34,640 - Data Goverance - INFO - 1\n",
      "2025-08-20 13:28:34,641 - Data Goverance - INFO - replace_currency_to_usd takes 1.4046480854352315 minutes\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "params_list = [([ctry], transtype_mapping_dict, from_dir, to_dir) for ctry in country_list]\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: replace_transtype(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result())\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'replace_currency_to_usd takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "d18789bd-34e2-4577-bdc2-4763e1385f65",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "channelno,dataid,iesign,datatype,writeoffflag,writeoffdataid,outputdate,origincountrycode,origincountry,countrycodeofdelivery,countryofdelivery,importername,importeraddress,importercontact,suppliername,supplieraddress,suppliercontact,hscode,hscodedescription,commoditydescription,totalcifvalue,totalfobvalue,grossweight,netweight,quantity,quantityunit,teu,importer_forwarderagent,supplier_forwarderagent,abnormaldata,portofloading,portofdestination,loadingcountrycode,loadingcountry,transportterm,tradeterm,paymentterm,carrier,containerno,vesselname,brand,version,country,IMPORTER_ID,SUPPLIER_ID\n",
      "zdzj,99e6dbc0c6404dab,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,350211000000,\"Dried, egg albumin\",EGG ALBUMIN,1394.4465972407233,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,becaaae087b5493d,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,800700000000,Other articles of tin.,TIN METAL GRANULARS,684.5465113734488,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,ec69430bb5dd4e28,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,170230000000,\"Glucose and glucose syrup,not containing fructose or containing in the dry state<20% by\",GLUCOSE POWDER MONO,91.91177827170478,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,eba6069e6e614b1e,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,170199900000,\"Other:Cane or beet sugar and chemically pure sucrose, in solid form.\",SUCROSE POWDER,114.09108524229248,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,9cbaa6785cd54145,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,350510000000,Dextrins and other modified starches,STARCH POWDER,85.56324322522863,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,23bcb5dd70354cf0,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,701090000000,\"Other:Carboys, bottles, flasks, jars, pots, phials, ampoules and other containers\",DROPPER 5 MLS,19.015180860331068,,144.0,144.0,144.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,16d42e348799446d,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,842389000000,Other,LABORATORY DIGITAL WEIGHT BALANCE,2408.589577033907,,143.0,143.0,143.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,1c6975c270584195,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,283329000000,Other:Sulphates; alums; peroxosulphates (persulphates).:Other sulphates,IRON (II) SULPHATE,247.197351344916,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n",
      "zdzj,a94e8bd1d3924be4,I,D,o,,2025-01-31,,,TZA,TANZANIA,NICCIAN GROUP LIMITED,,,,,,283699000000,Other:Carbonates; peroxocarbonates (percarbonates); commercial ammonium ca,AMMONIUM CARBONATE,183.81341509027507,,142.0,142.0,142.0,KGM,,0,0,0,,,India,,,,,,,,,202501,TZA,127205515.0,\n"
     ]
    }
   ],
   "source": [
    "!head -10 dataset/data_20250724/20251t3_csv_correct1/坦桑尼亚/IMPORT/202501-IMP-RAW.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a600f143-a97a-4077-a171-bef2460ffb58",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "35152969-c756-4a11-a020-c0ccfe247272",
   "metadata": {},
   "outputs": [],
   "source": [
    "# transtype_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "66bf3f77-5e83-4ead-89bf-2d23c47118d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(transtype_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eabb8d19-cc91-4a65-a9de-53a97e27d2ec",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1bc8cae-ea8a-455c-b614-805739a1075d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e46fd8c-b769-460b-bcfe-d1f103567d34",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10b0519f-216d-45b2-aa8c-eb3f6d3aa7b1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "3e5294ce-9f93-48ed-85f7-197a6c1ab582",
   "metadata": {},
   "source": [
    "# 8 空值率与指标统计，并更新数字"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1a6a2c2-ea41-4a06-8da1-41c8753a858b",
   "metadata": {},
   "source": [
    "## 8.1 结果校验"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf936fa0-0f08-4340-809d-c498e37559c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['阿根廷', '埃塞俄比亚', '巴基斯坦', '巴拉圭', '巴拿马', '博兹瓦纳', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '印度', '英国', '越南', '智利']\n",
    "# country_list = ['秘鲁-空运', '秘鲁-海运', '科特迪瓦', '莱索托', '乌干达', '乌克兰']\n",
    "#     , '加纳', '喀麦隆', '菲律宾', '马拉维']\n",
    "# country_list = ['阿根廷', '埃塞俄比亚', '巴基斯坦', '巴拉圭', '巴拿马', '博兹瓦纳', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '英国', '智利']\n",
    "# country_list = ['印度']\n",
    "# country_list = ['尼日利亚', '博兹瓦纳']\n",
    "# country_list = ['厄瓜多尔', '科特迪瓦', '斯里兰卡', '乌克兰', '越南']\n",
    "# country_list = ['阿根廷', '菲律宾']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2fc7f3c-0c75-4ef1-a065-5178164f97a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_correct1')\n",
    "# correct_csv_path = Path('dataset/data_20250724/20251t3_csv_correct1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bb75928-e631-476e-b280-e614f2ce3b1e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acb07b45-1c93-4164-bd17-7070943589ff",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "323b566d-957f-4ceb-ba5e-e8d9cdf18909",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['坦桑尼亚']\n",
    "# country_list = ['印度']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be7a8d6e-613f-4e7d-a1bc-d5d7a1a625bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_correct1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1af6bcb8-cee8-433b-a759-e541bb7df24a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# stat_rslt0 = compute_stat2(country_list, csv_files_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f8e65de-3014-4dc2-bd1d-4854c1bad679",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# stat_rslt0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38067441-d455-4cf1-bf3a-01122c7cdbde",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stat_rslt0['cif总价']/100000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74357710-b645-4dee-b201-bffed1c437a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stat_rslt0['fob总价']/100000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ad68fd4-97d4-4edb-a5c2-9b854127c680",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82864cac-0a2f-4c41-84e4-34b883569b56",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfb177a0-06fc-4117-a1fd-fe902a2a9070",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import threading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cd0cc87-5bec-4df0-bc02-6ff582b45e3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from joblib import Parallel, delayed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26f913de-8799-42ed-b6fb-178a6a5263ba",
   "metadata": {},
   "source": [
    "## 8.2 总和指标计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cea3c8e7-839f-4afc-a348-e75775489517",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "                , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "                , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "                , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "                , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "\n",
    "csv_files_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/files3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93586ddf-6c60-40fd-bf88-000d2b5720b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['俄罗斯陆运']\n",
    "\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/RUS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21d0d01f-007f-4c65-a803-e9dbb37adc92",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# params_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "322d792b-eb24-47c3-99e0-ce9a24602ed1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# start = time.time()\n",
    "\n",
    "# params_list = [([ctry], csv_files_path, country36_dict) for ctry in country_list]\n",
    "# results = []\n",
    "# with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "#     # 提交多个任务\n",
    "#     futures = [\n",
    "#         executor.submit(lambda p: compute_stat4(*p), params)\n",
    "#         for params in params_list\n",
    "#     ]\n",
    "#     # 按完成顺序获取结果\n",
    "#     for future in as_completed(futures):\n",
    "#         res = future.result()\n",
    "#         logger.info(res.shape)\n",
    "#         results.append(res)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "965f8eb7-54b8-4ada-ba48-8dbe62a4c9de",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stat_rslt1 = pd.concat([x for x in results], axis=0, ignore_index=True)\n",
    "\n",
    "# end = time.time()\n",
    "# logger.info(f'get_type_enumvalue takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13d2681a-6c8b-4c90-a2c2-af0d9e3f4344",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 单线程\n",
    "# stat_rslt1 = compute_stat4(country_list, csv_files_path, country36_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69fe5b33-ff94-4ab8-89f7-e1f0d8eba209",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stat_rslt1.to_csv('result/statistic_result_sum_20250810.csv', sep=',', index=False, header=True, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b9fb37b-188d-4d9e-b104-7fe6c7924830",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# stat_rslt1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "761d9e88-ff0f-4c68-9256-e5dc0ba8db00",
   "metadata": {},
   "outputs": [],
   "source": [
    "# future.result().shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c699184-2580-4723-81b7-b380d2fb8460",
   "metadata": {},
   "source": [
    "## 8.3 空值率指标计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15b35f4a-f893-43b7-82a2-4799092cf839",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "                , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "                , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "                , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "                , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_final/files')\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/final_files')\n",
    "csv_files_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/files3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad3b2c4c-1deb-42da-b708-1f0128215f74",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['俄罗斯陆运']\n",
    "# csv_files_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/RUS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad3ecb31-8190-49e5-9c60-865eda31a9c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stat_rslt2 = compute_stat5(country_list, csv_files_path, country36_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dee180d9-2862-4db4-b03f-1b129bee5632",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "params_list = [([ctry], csv_files_path, country36_dict) for ctry in country_list]\n",
    "\n",
    "results = []\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: compute_stat5(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        res = future.result()\n",
    "        logger.info(res.shape)\n",
    "        results.append(res)\n",
    "\n",
    "stat_rslt2 = pd.concat([x for x in results], axis=0, ignore_index=True)\n",
    "# stat_rslt2\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'get_type_enumvalue takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0f48548-5c4b-41a5-bceb-e055d0bcbb27",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55af4ddb-0bfa-4cf2-ad1e-0394450cf6c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stat_rslt2.to_csv('result/statistic_result_20250803_2.csv', sep=',', index=False, header=True, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3549e3f8-0421-4ff9-a1dd-0f78589e87be",
   "metadata": {},
   "outputs": [],
   "source": [
    "stat_rslt2.to_csv('result/statistic_result_rus_20250811.csv', sep=',', index=False, header=True, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "384e3d14-9211-41f7-a23c-1024c00e8792",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "stat_rslt2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca44b1d4-dd32-4216-8b92-59e093c9bdea",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52802231-a7f5-4877-9278-199939970fd9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d342a52c-d4e7-491b-bbd7-82ebc1df4d32",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d598c13-4a73-4ce4-8e15-a56aeca99cd9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "04b3395e-9c1c-4648-8b1b-fe38473c1ad8",
   "metadata": {},
   "source": [
    "# 9 获取HS码映射关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b095ffc-47d2-4a8e-9db3-7527c6021417",
   "metadata": {},
   "outputs": [],
   "source": [
    "# hscode\tstring\ths编码\t\tHS编码和产品描述至少一个不为空。\n",
    "# hscodedescription\tstring\ths编码描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd501e74-e687-4031-9165-94b50edcd1f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from_path = Path('dataset/data_20250724/20251t3_csv_correct1')\n",
    "to_path = Path('result/20251t3_hs_mapping')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52d02c0b-b4c9-4bc1-ae2a-0f807ad89cde",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "                , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "                , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "                , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "                , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "# country_list = ['尼日利亚', '博兹瓦纳']\n",
    "# country_list = ['阿根廷']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51a075b0-d2e9-4b79-a93b-e45fc41e9a52",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# get_hs_mapping(from_path, to_path, country_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d060e876-48fd-4612-9dca-3fdc2d228b07",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "params_list = [(from_path, to_path, [ctry]) for ctry in country_list]\n",
    "\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: get_hs_mapping(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result().shape)\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'get_type_enumvalue takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab74dae3-3681-43a3-9e01-310981f9c1a5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6ab1d982-3dd8-4f50-bcb9-e8b4ef73027c",
   "metadata": {},
   "source": [
    "# 10 获取成交方式、运输方式、付款方式、"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be3db185-dbf5-4568-aa60-8b1127378e00",
   "metadata": {},
   "outputs": [],
   "source": [
    "# transportterm    运输方式\n",
    "# tradeterm    成交方式\n",
    "# paymentterm    付款方式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "970730e1-92bc-40b1-9b4f-2d17552668af",
   "metadata": {},
   "outputs": [],
   "source": [
    "from_path = Path('dataset/data_20250724/20251t3_csv_correct1')\n",
    "to_path = Path('result')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e0843a1-855d-4358-bebe-4eb662be9a17",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "                , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "                , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "                , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "                , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']\n",
    "# country_list = ['阿根廷']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed8b9225-2af5-4a0a-826e-ae6495f086ac",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# rslt = get_type_enumvalue(from_path, to_path, country_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2847fd74-377e-4a6c-be00-14448f1ed937",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "params_list = [(from_path, to_path, [ctry]) for ctry in country_list]\n",
    "\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: get_type_enumvalue(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        logger.info(future.result().shape)\n",
    "\n",
    "rslt = pd.concat([future.result() for future in futures], axis=0, ignore_index=True)\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'get_type_enumvalue takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7739976a-4002-4170-bee3-3f2050dc52d6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49bec0f3-f253-4585-9e3b-59e34ec76b8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rslt.to_dict('records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5dac3146-6b0c-4137-af4c-bbbc46fa2702",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "rslt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9a2da26-b3b1-4ebc-9e66-b50d0aad6a9f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a004e095-37f4-4b21-b56f-bf5005c6b3d7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b78e1669-6305-4fd5-8368-f558bea09d78",
   "metadata": {},
   "source": [
    "# 11 上传的csv文件格式处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27b6a4a6-3359-4452-9709-9a0aec3cc295",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !head -3 dataset/data_20250724/20251t3_csv_final_tmp/files/ARG_202501_EXP.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8ac3226-9762-4aaf-9a99-8cccb2b4323e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7fe45d2-7ebc-469f-87eb-0ef358910619",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6471fa36-2d86-4ae8-9dda-a14a686d71cd",
   "metadata": {},
   "source": [
    "## 11.1 去掉\"\\\\N\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a8ac44e-faec-40ba-aa71-1f8da8826088",
   "metadata": {},
   "outputs": [],
   "source": [
    "# country_list = ['印度', '英国', '越南', '阿根廷', '埃塞俄比亚', '巴基斯坦', '俄罗斯陆运', '厄瓜多尔'\n",
    "#                 , '菲律宾', '哥伦比亚', '哥斯达黎加', '哈萨克斯坦', '加纳', '喀麦隆', '科特迪瓦', '肯尼亚'\n",
    "#                 , '莱索托', '马拉维', '美国', '孟加拉', '秘鲁', '秘鲁-海运', '秘鲁-空运', '墨西哥'\n",
    "#                 , '纳米比亚', '尼日利亚', '斯里兰卡', '坦桑尼亚', '乌干达', '乌克兰', '乌拉圭', '乌兹别克斯坦'\n",
    "#                 , '智利', '巴拉圭', '巴拿马', '博兹瓦纳']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e79f6b8d-369c-4949-a1c3-f4f1c206e87e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 获取文件目录，区分出口、进口数据\n",
    "# from_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/final_files')\n",
    "\n",
    "# # file_tree = distinct_ie_data2(from_path, country_list)\n",
    "# to_path = Path('dataset/data_20250724/20251t3_csv_final/final_files')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39872c1b-733e-4e75-9e5f-bff06b031204",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_list = ['俄罗斯陆运']\n",
    "\n",
    "# 获取文件目录，区分出口、进口数据\n",
    "from_path = Path('dataset/data_20250724/20251t3_csv_final_tmp/RUS')\n",
    "\n",
    "# file_tree = distinct_ie_data2(from_path, country_list)\n",
    "to_path = Path('dataset/data_20250724/20251t3_csv_final/final_files')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e3ed767-71ca-4d4f-9be9-646ad0bbf4ae",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# params_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "645ba27e-01cc-410f-bda5-0a12baa2ef14",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "params_list = [([ctry], from_path, to_path, country36_dict) for ctry in country_list]\n",
    "\n",
    "with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "    # 提交多个任务\n",
    "    futures = [\n",
    "        executor.submit(lambda p: replace_tab_to_norm(*p), params)\n",
    "        for params in params_list\n",
    "    ]\n",
    "    # 按完成顺序获取结果\n",
    "    for future in as_completed(futures):\n",
    "        # logger.info(future.result().shape)\n",
    "        pass\n",
    "\n",
    "\n",
    "end = time.time()\n",
    "logger.info(f'replace_tab_to_norm takes {(end-start)/60} minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e67ef03c-0b6b-45c4-a6ac-bf9c59dc3531",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "params_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc5deb04-4284-4bed-ad2c-e7c587fd62d2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# replace_tab_to_norm(country_list, from_path, to_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a2bd569-e749-401e-ae3b-bc023b57d404",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea4a8b25-4b2e-4901-9d05-3d2d91772482",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ad6f55e-2f55-4f27-bed3-c4740ffa903e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "458375bc-4dda-4f46-94f1-9b074d5623ef",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aecbb604-0068-488b-9bc6-e27139355b9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !head -3 dataset/data_20250724/20251t3_csv_final/files/ARG_202501_EXP.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e2b1227-9fe0-4681-94f1-a9250b5b5418",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c938f96b-ebd7-4e93-a274-0d860fc43c99",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b2579018-80c6-4f11-a043-51d00e18d5d1",
   "metadata": {},
   "source": [
    "# 12  校验与更正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1f64c6a-6583-4be5-8a46-979b070c5d9c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c34b0a36-ed39-4614-8f11-8975ad8d14d6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# df = pd.read_csv('dataset/data_20250724/20251t3_csv_final_tmp/files2/ARG_202501_IMP.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65d6de7b-801f-4ec4-9819-207e53b32545",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# df.iloc[:,0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfbad041-1fc8-467b-821b-131ee3990cde",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.iloc[:,20:40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a757e04-b70d-4f2b-99f8-10850d3e3f18",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# !head -3 dataset/data_20250724/20251t3_src_version/巴基斯坦/import_paths/202501-IMP-FULL.csv\n",
    "# !head -3 dataset/data_20250724/20251t3_csv_correct1/印度/IMPORT/INDIA_IMPORT_202501_2105A.csv\n",
    "# !head -3 dataset/data_20250724/20251t3_csv_correct1/印度/IMPORT/INDIA_IMPORT_202501_2105A.csv\n",
    "# !head -3 dataset/data_20250724/20251t3_csv_correct1/印度/EXPORT/INDIA_EXPORT_202503_2106B.csv\n",
    "# !head -3 dataset/data_20250724/20251t3_csv_final/files/UZB_202502_IMP.csv\n",
    "# !head -3 dataset/data_20250724/UZB_202501_IMP.csv\n",
    "# !head -3 dataset/data_20250724/20251t3_csv_final_tmp/final_files/BWA_202503_EXP.csv\n",
    "# !head -3 dataset/data_20250724/20251t3_src_version/美国/import_paths/us20250101-04.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "394cbf29-d12b-4384-aa9f-77e06a95d973",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# tab_tmp = pd.read_csv('dataset/data_20250724/20251t3_src_version/厄瓜多尔/import_paths/202501-IMP.csv'\n",
    "#                       , header=None\n",
    "#                       , sep=','\n",
    "#                       , quotechar='\"'\n",
    "#                       , lineterminator='\\n'\n",
    "#                       , on_bad_lines='skip'\n",
    "#                       , encoding='utf-8')\n",
    "# # tab_tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fba64fd9-21cc-427b-9be4-a9f925006ed3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# tab_tmp.iloc[:,0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24835f45-d9b3-4055-b18c-7621c24c6ec4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# tab_tmp.iloc[:,20:40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd563e53-0120-4544-8771-edacbc656d6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# tab_tmp.iloc[:,40:60]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40b5b0aa-6ff8-4a77-833b-8c12bd14d283",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25d661c7-ffa8-4268-9036-079ee2343caf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5536c451-499a-45b0-a485-92e1a81c6c34",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03fcc23a-76cc-4650-b536-ff07aaf081fa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f05bda2e-3ec0-433a-8143-878b7f93b56e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "col_type = get_col_str_type(config.target_cols[0:-2])\n",
    "# tab_tmp = pd.read_csv('dataset/data_20250724/UZB_202501_IMP.csv',encoding='utf-8',header=0,dtype=col_type)\n",
    "column_names = config.target_cols[0:-2]\n",
    "# dataset/data_20250724/20251t3_csv_final_tmp/final_files/BGD_202501_EXP.csv\n",
    "# dataset/data_20250724/20251t3_csv_final_tmp/final_files/BWA_202503_EXP.csv\n",
    "\n",
    "tab_tmp = pd.read_csv('dataset/data_20250724/20251t3_csv_final_tmp/final_files/BWA_202503_EXP.csv' \n",
    "                      , header=None, names=column_names\n",
    "                      , sep=','\n",
    "                      , quotechar='\"'\n",
    "                      , lineterminator='\\n'\n",
    "                      , on_bad_lines='skip'\n",
    "                      , encoding='utf-8',dtype=col_type)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c407168-97fc-4146-b3d4-6bf9d0a4b0e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "tab_tmp.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69ecf5ee-8980-4507-90a4-98f09439b54e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tab_tmp['iesign'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "229c3440-f9e2-45ae-a92c-86aef66e5fe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp1 = tab_tmp.groupby(by=['iesign'], as_index=True).agg({'iesign':'count'})\n",
    "tmp1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7834f9b-4e8f-4b7b-b0f4-826ac8987bea",
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp1.rename(columns={'iesign':'数据量'}, inplace=True)\n",
    "tmp1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc63f9da-5090-4a8e-9f9c-fbb81b819070",
   "metadata": {},
   "outputs": [],
   "source": [
    "# tmp = pd.concat([tmp1, tmp2], axis=0)\n",
    "# tmp.reset_index(level=0, inplace=True) \n",
    "# tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e6a0e5e-6f1f-411c-b58e-ac04d664b261",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ef7472d-0e4c-418b-91dd-308d511e2127",
   "metadata": {},
   "outputs": [],
   "source": [
    "tab_tmp['totalcifvalue'] = [extract_float_before_second_dot(x) for x in tab_tmp['totalcifvalue'].values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79b5034c-800e-49f6-8f48-8192bc71fb1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tab_tmp['totalcifvalue'].astype('float').sum()/100000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0796d1bb-3810-45d1-b2df-aa48d89a2cc8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90ce6d26-1bf4-4725-9829-9516e86fc22e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba9d8127-9f4d-4f89-acf3-8f61e9422cf6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09f5142e-a682-4ebd-b004-e3f7936cc8d5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1add603-e351-413f-a6d0-cd6f8ab899a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('dataset/data_20250724/UZB_202501_IMP.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48785f5c-b9ce-4e77-998b-4fa7cbfd5ddb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct1/印度/IMPORT/INDIA_IMPORT_202501_2105A.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0170c63d-593c-4cb8-b3d4-f0f8dc2630a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('dataset/data_20250724/20251t3_csv_correct1/印度/IMPORT/INDIA_IMPORT_202501_2105A.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d465753-2843-428c-9b24-e456282d5d65",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8458d19b-6b8a-4624-839b-421cc6c03152",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.iloc[:, 0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b862295b-f1a7-4862-8f6c-9c948611e9af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.iloc[:, 20:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08dd63a8-7d63-4c9f-9b75-99b7cff5fe20",
   "metadata": {},
   "outputs": [],
   "source": [
    "!head -3 dataset/data_20250724/20251t3_src_version/巴基斯坦/export_paths/202501-EXP-FULL.csv\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3034745-f0f5-498d-9a1e-18a591d0f192",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('dataset/data_20250724/20251t3_src_version/巴基斯坦/export_paths/202501-EXP-FULL.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d9dac8e-026c-44ac-877b-d08da05d2cd4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8c7a3f9-0294-499a-9c41-82348700b305",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bb17094-94a5-4e71-adc2-621d7b135de7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "580353fc-fb87-4a1f-9f50-13e210dbe556",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2560e7a6-8f6e-4a96-978d-dea704194a2f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "25a80fff-3bd3-44f0-be12-b36efa246b6a",
   "metadata": {},
   "source": [
    "# end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "893235b3-693f-47df-bad1-6256b394e865",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_end=time.time()\n",
    "logger.info(f'{(all_end-all_start)/60} minutes')\n",
    "logger.info('All done!\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "409ea3bf-dca2-4bd1-b59d-ef2045e32deb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# str_type = dict()\n",
    "# for col in config.target_cols:\n",
    "#     str_type[col] = 'str'\n",
    "# str_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c309bd2d-0ddb-4aeb-a788-672b2daea563",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
